Lookup on specific text in a cell to return value in another cell

[Mr Bulldops]

Hi, I have an issue whereby I download data each month and the text in these cells changes slightly each time so it’s not possible to use a standard vlookup. What I would like to do is lookup specific text in one cell to return a value in another.

For example the following is in these cells: A1: 401 chicken QQR A2: chicken A3: Nugget. I would like to use cell A2 to see if the word chicken is anywhere in cell A1 (which it is) and if so return what is in cell A3 “Nugget” in cell B1. A standard vlookup won’t work as the text in cell A1 contains more than just the text chicken.

I guess it’s a vlookup but only for a specific bit of text rather than all of the text. Would really appreciate if anyone could help on this as it’s frying my brain.

 

[arthurbr]

Like this ?
=IF(ISNUMBER(SEARCH(A2,A1)),A3,"")

If the case should be respected use FIND instead of SEARCH