If range values exist in cell, then... how?

[Camel123]

Hi,

Sheet 1/Column A: Invoice description + supplier name
Sheet 2/Column A: Supplier list

If any of the names in the supplier list (range) match with the (cell) value in column A, I want that to retrieve that name to Sheet 1/Column B. The opposite would be much easier but the problem is that values in sheet 1/Column A may look like this "9935676747/33 44 5-55 66 McDonald's", "Starbucks 445-1 Coff.", "McDnl 111" or "993-1 KFC", no standard on setup of numbers, length of text string etc.

Suggestions?

 

[Fluff]

How about
=INDEX(Sheet2!$B$2:$B$100,MATCH(1,--(ISNUMBER(SEARCH(Sheet2!$A2:$A$100,A2))),0))

Confirmed with Ctrl Shift Enter

 

[Camel123]

Sorry I forgot to mention that in the supplier list, only name appears, no numbers. On the other hand, the invoice description includes a lot of numbers etc. and the name might not be an exact match to the one in the list. McDonald's in the supplier list might be "McDon", "Mc Donalds", "McDnlds" in the invoice description.

 

[Fluff]

In that case you'll need to sort out your data.
Failing that you will need some sort of Fuzzy lookup, there are some add-ins available on the net that may help.