If Vlookup array table contains NOT wildcard

[thedeadzeds]

Hi Guys, I'm trying to do a lookup up but rather than do a wildcard lookup I want to turn it around.

For example, I have a table lets say rows A1 to A5 which contains certain words. If any of these words or a version of these words are found in a cell I want to pull this through. So, Table one below is my lookup table and table 2 is the raw data table. I would like the result to show as per the result column 2 in the raw data table? So a formula in column 2 (lookup found)

Lookup Table

Car

Rent

Finance

Motor

Water

<colgroup><col></colgroup><tbody>
</tbody>

RAW DATA

Data	Lookup found?
Rental Red	Yes	as it contains rent
CJ Finance	Yes	as it contains finance
Motoring	Yes	as it contains Motor
Blue Watering	Yes	as it contains Water

<colgroup><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col></colgroup><tbody></tbody>

 

[MrJoosten]

Hi,

Try this:

=VLOOKUP("*"&A3&"*";$A$15:$A$19;1;FALSE)

assuming your RAW DATA is from A15 to A19.

 

[thedeadzeds]

Sorry, think i may have not explained this correctly, my current lookup is:

=VLOOKUP(M6, 'Fleet Supression'!A2:A15, 1, FALSE)

but essentially what i want is if M6 contains any of the words or a simular word in 'Fleet Supression'!A2:A15' then false

hope this makes sense