Index Match with Multiple Criteria while looking for a certain string-CodePudding

   A          B          C
1  Name       Last Name  ID
2  Ben        Dafflin    ID1001
3  Yu         Yiin       ID1002
5  Max        Gray       ID1003
6  John Carl  Flit       ID1004

Situation 1 : Index Match with wildcards "*" (Working Fine!)
Formula    : =INDEX($C:$C,MATCH("*John*",$A:$A,0))
Result      : ID1004


Situation 2 : Index Match with multiple criteria (Working Fine!)
Formula     : =IFERROR(INDEX(D:D,MATCH(1,(B:B="Flit")*(C:C="John Carl"),0)),"")
Result      : ID1004

Problem: In situation 2, If i will only look for cells with "Carl" or "Carl" it doesn't work. Any suggestion how will I use the situation 2 having an index match with multiple criteria but can still look for cells that contains such specific string.

CodePudding user response：

To have your 2nd formula return a value, you just need to reference the correct columns: =IFERROR(INDEX(C:C,MATCH(1,(B:B="Flit")*(A:A="John Carl"),0)),"")

If you want to use wild cards with MATCH where you have multiple criteria, you can try:

=INDEX(C:C,MATCH(1,1/(ISNUMBER(SEARCH("*Carl*",A:A))*(B:B="Flit")),0))

If you have Office 365

=FILTER(C:C,ISNUMBER(SEARCH("*Carl*",A:A))*(B:B="Flit"))

CodePudding user response：

Lookup when Matching to Multiple Columns
There's another way to approach this, which is simpler and may well be a better fit:

{=INDEX(C:C,MATCH("*CarlFlit",A:A&B:B,0))}

The above is of course an array formula (hence the opening { and closing })
a) The array matched is the rows of columns A and B concatenated into single strings
b) This specific example is of course looking for (*wild-carded) *CarlFlit
c) Which of course, could be any combination of values and valid MATCH wildcards

If the values being looked for are in cells (rather than hardcoded into the formula) simply refer to those cells:

{=INDEX(C:C,MATCH(E2&F2,A:A&B:B,0))}

To add checking for no-match (using first example above):

{=IF(ISNUMBER(MATCH("*CarlFlit",A:A&B:B,0)),INDEX(C:C,MATCH("*CarlFlit",A:A&B:B,0)),"Not Found")}

Additional information
The formula above can be expanded to accommodate matching across any number of columns.
o Simply & in each additional column (in whatever order suites your purpose).

For performance reasons, it's not a good idea to have lookup functions looking at entire columns.
a) Better to use (e.g.) {=INDEX($C$1:$C$1000,MATCH("*CarlFlit",$A$1:$A$1000&$B$1:$B$1000,0))}.
b) Where 1000 is of course an example last row in your lookup range.