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

Arielariela

Not tried with Match(), but the official Union operator is ,. So it should be A1:A7, A9:A20 – Edwinaedwine

@PatrickHonorez The official union operator is a comma in Excel? Commas are used in Excel to separate arguments in a function, not to concatenate arrays? – Foreignism

@Foreignism of course it is. Try writing =SUM(F2:F4, S2:S4). Web is full of examples: learn.microsoft.com/en-us/office/vba/api/… or dailydoseofexcel.com/archives/2005/01/16/union-and-intersect – Edwinaedwine

@PatrickHonorez Ah, I wasn't aware that comma was called union operator in certain Excel functions. MATCH is not one of these. This question and answer cleared it up. – Foreignism

JvdV has provided a good answer in the case of excluding one specific cell within a continuous single column/row range but I think maybe it is simpler to just use two MATCH function for each range without the need of using array formulas. – Lillielilliputian

@TerryW, true that. Included in my answer now :) – Convery

Would you be comfortable in adding everything A1:A20 then subtract A8 afterwards? – Wheelman

@uom-pgregorio the values in A1:A20 are not numbers but formulas – Arielariela

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 =)

Convery

Array within Match, very nice, I always default to SUMPRODUCT on this but your way is cleaner and shorter. – Alehouse

Technically this doesn't answer the question. This answers "how to find a MATCH excluding a certain row?" instead of "how to find a MATCH of two concatenated arrays?" but... maybe this is what the OP wants. I guess we will find out. Still a good answer. +1 – Foreignism

@ImaginaryHuman072889, I understand where you comming from, but the title states "...without specific cell". I also don't see where OP concatenates two ranges, rather than trying to exclude A8, being that one cell. Thanks for the kudo's :) – Convery

Thank you all for your fast and reliable answers... will check it out tommorrow and report back to you what worked for my situation- Thanks in advance – Arielariela

@Convery honestly it would interest my how two concatenated ranges work as well but as for my current issue i only need a single colum range without a specific cell... nonetheless i am grateful for both answers and will try them respectfully out – Arielariela

Somehow these formulas interfered with my ISNUMBER and didn't work out. I tried it with two MATCHes. idk why i didn't think of that before, and it seemed to work out. Thank you all for your hard work – Arielariela

@JayJay, I have edited the answer to accomodate your question on how to exclude a certain range of cells, or in other words "concatenate" two ranges. – Convery

Could you please explain how (A1:A20="X")*(ROW(A1:A20)<>8) works? – Semidiurnal