Search code examples
excelexcel-formularangecellstring-matching

Excel MATCH range without specific CELL

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

Solution

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