Formula Help Pls - Search cell for WORD, return fields from WORD lookup

Hello all,

I've been working for hours on a formula to search for a list of words within a field, and if it finds one of those words, to lookup and return a couple of fields. Sounded so easy, but when i got into it, it really wasnt for me! Really hope someone can help me.

I've attached a document template with instructions and example data for the formula i'm trying to build. Essentially it is:

1. In Column N above e.g. cell N3, formula should:
•look up and match words from "1.1 Lookup" tab, column H21:H100 e.g. "Description 3", "Description 4", etc
•match this against "1) I&E" tab, column C e.g. cell C3, "Description 3"
•formula should then return corresponding Category from"1.1 Lookup" tab, column i e.g. cell i21 "Shopping"

2. Repeat the same for "Sub Category"

Any help you can give would be appreciated :-)

Thanks in advance,
Tom

Originally Posted by Glenn Kennedy

and a formula solution:

=IFERROR(LOOKUP(1000,SEARCH('1.1) Lookup'!$H$21:$H$24,$C3),'1.1) Lookup'!I$21:I$24),"")

in N3, copied across and down.

Thank you both for you replies... Glenn, you legend, it works perfectly!!!