CODEWITHSUNDEEP
excelexcel-formulamatchstring-length
Scott Holtzman
Scott Holtzman

Reputation: 27239

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.

Upvotes: 0

Views: 881

Answers (1)

barry houdini
barry houdini

Reputation: 46331

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

Upvotes: 1

Related Questions