0

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.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72

1 Answers1

1

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81