Index match with multiple values

[Jer91]

Please see attached.

I need help with my index match formula to lookup one value in a column that has multiple values per cell. I need to lookup the values in sheet2 column C in sheet1 column C (the column with many values per cell, usually separated by a semicolon or comma) and if there is a match return sheet1 column A&B to sheet2 column A&B. A regular index match doesn't seem to work with multiple values in one cell.

Any help would be appreciated as I wasn't able to find a solution myself.

Thanks

[FDibbins]

This will find some...but not all exist in sheet 1...
=INDEX(Sheet1!B$2:B$24,IFERROR(MATCH("*"&$C3&"*",Sheet1!$C$2:$C$24,0),MATCH($C3,Sheet1!$C$2:$C$24,0)))

To get rid of the error messages...
=IFERROR(INDEX(Sheet1!B$2:B$24,IFERROR(MATCH("*"&$C3&"*",Sheet1!$C$2:$C$24,0),MATCH($C3,Sheet1!$C$2:$C$24,0))),"")

[Jer91]

Thank you so much FDibbins!

[FDibbins]

Happy to help, thanks for the feedback