Lookup a string of text in one sheet and return adjacent value from another sheet

[DianaD]

I have one sheet with a string of text that I want to lookup on another sheet and return the adjacent value, similar to VLOOKUP but looking for a string within the cell.

Looking for C3 from sheet #1 within column B of sheet #2 and then return the adjacent value from column A on sheet #2.

I hope this makes sense.

regards,
DianaD

 

[arun_eagertolearn]

Please try

=xlookup("*"&A3&"*"&B3&"*",sheet2!B:B, sheet2!A:A,"not found",2)

(assuming the entries start from row3- cells A3 and B3---where 1/2 and 150# are and the other sheet is "sheet2")

* and the 2 at the end of xlookup make it a wild card search