adding criteria for correct match

[gtd526]

Hello,
This formula is working, but I need to add another criteria.
In addition to MATCH("*"&C2&"*",'Player Stats'!C:C,0),
want to add: MATCH(B2,'Player Stats'!A:A,0)
This way its looking at 2 matches (A:A & C:C) in different columns for a correct result (AC:AC).

This is the complete formula.

=INDEX('Player Stats'!AC:AC,MATCH("*"&C2&"*",'Player Stats'!C:C,0))

You can create a different formula (not using Index, Match) if needed.

thank you

 

[barry houdini]

Try this version

=INDEX('Player Stats'!AC:AC,MATCH(1,ISNUMBER(SEARCH(C2,'Player Stats'!C:C))*('Player Stats'!A:A=B2),0))

In Excel 2019 and earlier Excel versions this formula needs to be "array entered", i.e. confirm with key combination CTRL+SHIFT+ENTER

 

[gtd526]

Try this version

=INDEX('Player Stats'!AC:AC,MATCH(1,ISNUMBER(SEARCH(C2,'Player Stats'!C:C))*('Player Stats'!A:A=B2),0))

In Excel 2019 and earlier Excel versions this formula needs to be "array entered", i.e. confirm with key combination CTRL+SHIFT+ENTER

Perfect. Thank you.