Tabled Lookup

Hi,
Is anyone able to help?

I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.

As an eg.

Column G:
VAT payment
HMRC payment
Pay VAT

I have a table on the side that shows:
Column Y Column Z
VAT HMRC
HMRC HMRC

ie. If anything in column G matches one of the words in Column Y, then output the Column Z.

I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.

Any help greatly appreciated.

Thanks

Try:

Please Login or Register  to view this content.

where your table is in Y2:Z3 and your lookup string is in G2

confirm the formula with CTRL+SHIFT+ENTER not just ENTER.. then copy down

Because it is an "array formula". See here for more: Array Formulas