Returning a value based on a cell with a partial match

Hi

I was wondering if you could help me with a problem.. Iam trying to perform a lookup when the LOOKUP VALUE exists in the TABLE ARRAY among other text.. You will understand what i mean if you look at the sheet i have attached.


odds experiment.xlsx

CELL A 3 is 'Parma'
CELL D 10 contains 'Parma' as well as 'AC Milan v'

I want the lookup in Cell B3 to return the contents of CELL E10 if the contents of CELL A3 exists in a range of cells in Column D (if you get what i mean)

The next example could be 'Crotone' which would be in CELL A4, which also exists in D19, so i would want the formula in B4 to return the contents of E19

Any help if this can be done would be greatly appreciated.

You can use wildcard characters with VLOOKUP, so you could put this in B3:

=VLOOKUP("*"&A3&"*",D2:E222,2,FALSE)

which returns Yes (1) in this instance. Note that if the word is duplicated in other cells, VLOOKUP will only find the first occurrence.

Hope this helps.

Pete

Originally Posted by Pete_UK

You can use wildcard characters with VLOOKUP, so you could put this in B3:

=VLOOKUP("*"&A3&"*",D2:E222,2,FALSE)

which returns Yes (1) in this instance. Note that if the word is duplicated in other cells, VLOOKUP will only find the first occurrence.

Hope this helps.

Pete

excellent, many thanks pete, this was exactly what i was looking for.