As far as I know, to make a precise connection, one has to invoke Hodge theory. Suppose
that $X$ is a compact smooth projective variety of dimension $d$. Then Poincare duality pairs $H^n(X,\mathbb C)$ with $H^{2d-n}(X,\mathbb C),$ for any $n$.
Now the Hodge decomposition gives
$$H^n(X,\mathbb C) = \oplus_{p+q = n}
H^q(X,\Omega^p)$$
and
$$H^{2d-n}(X,\mathbb C) = \oplus_{p'+q' = 2 d - n} H^{q'}(X,\Omega^{p'})
= \oplus_{p + q = n}H^{d-q}(X,\Omega^{d - p}).$$
Now Serre duality gives a duality between
$H^q(X,\Omega^p)$ and $H^{d - q}(X,\Omega^{d-p}),$
and the compatibility statement is that Poincare duality between $H^n$ and $H^{2 d - n}$
is induced by the direct sum of the pairings on the various summands in the Hodge decomposition given by Serre duality. (Perhaps up to signs and powers of $2 \pi i$,
which I'm not brave enough to work out right now.)
Added: A good case to think about for a newcomer to Hodge theory is the case when $X$ is
a compact Riemann surface (or equivalently, an algebraic curve). If the genus of $X$ is $g$,
then $H^1(X,\mathbb C)$ is $2g$-dimensional, and is endowed with a symplectic pairing via
Poincare duality.
Hodge theory breaks $H^1(X,\mathbb C)$ up into the sum of two $g$-dimensional subspaces, namely $H^0(X,\Omega^1)$ and $H^1(X,\mathcal O)$. These are isotropic under Poincare duality (i.e. the Poincare duality pairing vanishes when restricted to either of them),
but the become dual to one another under Poincare duality, and that pairing agrees with
the Serre duality pairing (up to a factor of $2\pi i$, perhaps).
The easiest part of this to understand is the inclusion $H^0(X,\Omega^1) \subset
H^1(X,\mathbb C)$: a holomorphic differential gives a cohomology class just via de Rham
theory (i.e. we integrate the holomorphic one form over 1-cycles); note that holomorphic
1-forms are automatically exact, because if you apply the exterior derivative, you get a holomorphic 2-form, which must vanish (because $X$ is a curve, i.e. of complex dimension
one).
To see why $H^0(X,\Omega^1)$ is isotropic under Poincare duality, note that in the de Rham
picture, the Poincare duality pairing corresponds to wedging forms. But wedging two holomorphic 1-forms again
gives a holomorphic 2-form, which must vanish (as we already noted).
Let me address the isomorphism which you are after:
$$H_{n-k}(M-\Sigma)\cong H^{k}(M,\Sigma),$$
where $n$ is the dimension of $M$.
Let $A$ be an open tubular neighborhood of $\Sigma$ in $M$ (a disk bundle over $\Sigma$). Then, by excision, and since $A$ deformation-retracts to $\Sigma$, we obtain:
$$
H^k(M-A,\partial (M-A))\cong H^k(M,A)\cong H^k(M,\Sigma).
$$
Using the APL duality theorem, we obtain:
$$
H^k(M,\Sigma)\cong H^k(M-A,\partial (M-A)) \cong H_{n-k}(M-A)\cong H_{n-k}(M-\Sigma).
$$
Now, taking $k=n-1$:
$$
H^{n-1}(M,\Sigma)\cong H_1(M-\Sigma).
$$
Best Answer
It is an intersection pairing. A triangle and a line segment in $3$-space are dual if they intersect in one point. Likewise two line segments in $2$-space are dual if the intersect is a point. In both cases a single point is dual to the $2$ or $3$ dimensional simplex that contains it (usually as barycenter).
If you think of it as defined by the cap product with the cycle $[M]$, then to an simplex in say $n$ dimensional space you take its geometric complement, meaning for example if you have a triangle in space its complement is the line segment passing through the interior of the triangle, and conversely.
This kind of duality in euclidean geometry where a $k$ dimensional subspace is paired with an $n-k$ space such that together the span $n$ space (orthogonal, if you want) was known before Poincare, and I believe it was one of his motivations.