I don't know of a reference, but here is a quick argument. Suppose we want to compute the homotopy pullback P = X ×hZ Y of two maps f : X → Z and g : Y → Z of pointed simplicial sets. Assume for convenience that everything is fibrant. There is a fibration ZΔ[1] → Z∂Δ[1] = Z × Z with fiber ΩZ. Now P is the pullback of the diagram X × Y → Z × Z ← ZΔ[1]. In particular, P → X × Y is also a fibration with fiber ΩZ, and the Mayer-Vietoris sequence follows from the long exact sequence of homotopy groups of this fibration.
To be safe, let me assume the cohomologies are taken with coefficients in a field, like $\mathbf{C}$.
Let $I' \subset I$ be the indices for which $U_i$ is nonempty. The incidence algebra of $I'$ is a finite-dimensional algebra that naturally acts on the vector space of $\mathbf{C}$-valued functions on $I'$. Your "categorified Mobius inversion" amounts to finding the minimal projective resolution of this module.
Let $f:X \to I'$ be the function that carries $x$ to the index of $\bigcap_{i \in I \mid x \in U_i} U_i$. This function is continuous for topology on $I'$ whose open subsets are order ideals. The Mayer-Vietoris spectral sequence for the cover is also the Leray spectral sequence for the map $f$ and the constant sheaf .
$$
E_2^{st} = H^s(I';R^t f_* \mathbf{C}) \implies H^{s+t}(X)
$$
A sheaf on a finite topological space like $I'$ is the same data as a functor out of $I'$ regarded as a poset, and is also the same data as a module over the incidence algebra of $I'$. If $\mathcal{F}$ is a sheaf, the corresponding functor $F$ is given by the formula
$$
F(i) = \Gamma(\text{minimal open neighborhood of $i$};\mathcal{F})
$$
The corresponding module $M$ is the direct sum of all the $F(i)$. Under this correspondence:
- The sheaves $R^t f_* \mathbf{C}$ take the value $H^t(U_i;\mathbf{C})$ at $i$.
- Projective modules over finite dimensional algebras have a Krull-Schmidt property. In the case of the incidence algebra the indecomposable projectives are parametrized by $i \in I'$. The projective $P_{i}$ is given by
$$
P_i(j) = \begin{cases}
\mathbf{C} & \text{if $j \leq i$} \\
0 & \text{otherwise}
\end{cases}
$$
Homomorphisms out of $P_i$ compute the value of the functor at $i$.
- The constant sheaf on $I'$ is the module $\mathbf{C}^{I'}$.
As $H^s(I';-) = \mathrm{Ext}^s(\text{constant sheaf},-)$, a projective resolution of $\mathbf{C}^{I'}$ gives a chain complex computing $H^s(I';-)$ and the $E_2$ page of the spectral sequence. The theory of finite-dimensional algebras says that there is a unique minimal resolution (it appears as a subquotient of any other projective resolution) of $\mathbf{C}^{I'}$, or of any other finite-dimensional module $M$. One computes it by taking the projective cover of $M$, call it $P_M \to M$, next taking the projective cover of the kernel of $P_M \to M$, and so on.
Best Answer
The Mayer-Vietoris sequence is an upshot of the relationship between sheaf cohomology and presheaf cohomology (a.k.a. Cech cohomology).
Let $X$ be a topological space (or any topos), $\mathcal U$ a covering of $X$. Let $\mathop{\rm Sh}X$ be the category of sheaves on $X$ and $\mathop{\rm PreSh}X$ the category of presheaves. The embedding $\mathop{\rm Sh}X \subseteq \mathop{\rm PreSh}X$ is left-exact; its derived functors send a sheaf $F$ into the presheaves $U \mapsto \mathrm H^i(U, F)$. For any presheaf $P$, one can define Cech cohomology $\mathrm {\check H}^i(\mathcal U, P)$ of $P$ by the usual formulas (this is often done only for sheaves, but scrutinizing the definition, one sees that the sheaf condition is never used). One shows that the $\mathrm {\check H}^i(\mathcal U, -)$ are the derived funtors of $\mathrm {\check H}^0(\mathcal U, -)$; and of course for a sheaf $F$, $\mathrm {\check H}^0(\mathcal U, F)$ coincides with $\mathrm H^0(\mathcal U, F)$. The Grothendieck spectral sequence of this composition, in the case of a covering with two elements, gives the Mayer--Vietoris sequence.
There is also a spectral sequence for finite closed covers, which is obtained as in anonymous's answer.
I guess that this can also be interpreted as Tilman does, in a different language (I am not a topologist).