Search code examples
excelexcel-formulamatchstring-length

Excel Match on Multiple Criteria with Characters Length > 256

I am trying to build of this answer and incorporate it for multiple matches.

I have a formulas like this: 

=MATCH(TRUE,INDEX(G:G=A2&E:E=B2,0),0)
=MATCH(TRUE,AND(INDEX(G:G=A2,0),INDEX(E:E=B2,0)),0)

but it's not working.

How can I extend this to incorporate two or more match criteria that will capture values I have > 256 characters.

Solution

  • AND function doesn't work in these sort of formulas because it returns a single result, not an array You can use * to multiply the conditions like this

    =MATCH(1,INDEX((G:G=A2)*(E:E=B2),0),0)

    Note: Lookup value needs to be 1 because multiplying Booleans gives you 1/0 values

    INDEX function isn't strictly necessary - just used here to avoid "array entry". You can also use this version

    =MATCH(1,(G:G=A2)*(E:E=B2),0)

    but that requires CTRL+SHIFT+ENTER