Excel MATCH range without specific CELL

Question

after a deep search on the internet i gave up.

My "simple" question would be: How can I add two ranges in a formula, preferably in MATCH?

I want to search a range like A1:A7 + A9:A20 and thus not include A8 in my range.

Is this possible? Please help me out

Answer 1

Natively you can't but you could try to bypass it with either:

---

Exclude a single cell:

If you want to exclude a certain cell from a MATCH you can exclude it's certain row number like so:

=MATCH(1,(A1:A20="X")*(ROW(A1:A20)<>8),0)

Or

=MATCH(1,INDEX((A1:A20="X")*(ROW(A1:A20)<>8),),0)

Both are array formulas but only the first one needs to be confirmed by holding down Ctrl+Shift before pressing Enter

---

Exclude a range of cells:

If you would want to exclude a range of rows, e.g. 8-12, then we cannot use the AND or OR logic as they don't return an array. However, we can mimic it ourselves like:

=MATCH(1,(A1:A20="x")*((ROW(A1:A20)<8)+(ROW(A1:A20)>12)),0)

Or

=MATCH(1,INDEX((A1:A20="x")*((ROW(A1:A20)<8)+(ROW(A1:A20)>12)),),0)

Where, again, both are array formulas but only the first one needs to be confirmed by holding down Ctrl+Shift before pressing Enter

---

Alternative

Alternatively, you could stack MATCH functions in a number of ways, but here is one of them:

=IFERROR(MATCH("x",A1:A7,0),MATCH("x",A13:A20,0))

• Pro: No array formula > Fast!
• Con: Will return a match's relative row position in either the first or second range. In this example you could just add 12 to the second result.

Good luck =)