If, isnumber, lookup, search formula problem

**jamieray** · 09-07-2011, 07:09 PM

Hi All,

Im having a few problems with this formula and its killing me:

=IF(ISNUMBER(VLOOKUP(SEARCH(foxtype,B2,),table,2,FALSE),"missing from table")

I have put this together from various different threads i read hoping it will work but im getting an error message.

What im tring to acheive is this:

I have a string of text in cell B2 'Concept Shoe BLACK/GREEN 8' (its a stock list) and lots of other random produt names in column B, what i need to do is get the formula in C2 to look at B2 for a certain word that i have in a table (foxtable - Fox is the clothing brand and i have a list in there of the key words to search i.e Shoe, Jean, Pant, Tee, Knit, Polo ect this is on a seperate tab) and then return a word that is in the next column of that table (Table is the combination of Foxtable on another tab in colum A with what i need returned in Column B)

Im sure i have made this as clear as mud!!

Any questions please ask, all help greatfully received.

Jamie

**Andrew-R** · 09-08-2011, 04:49 AM

If I've understood you right then:

Column B contains a list of product descriptions
Table contains two columns - the first is a product type (shoe, jean, pant, etc.), the second is some sort of description
Foxtype - is a named range consisting of just the first column of Table, which contains key words, which may match part of the description in column B.

If that's all right then the formula for cell B2 is =IF(ISERROR(MATCH(TRUE,INDEX(NOT(ISERROR(SEARCH(Foxtype,B2))),0),0)),"Missing from table",INDEX(Table,MATCH(TRUE,INDEX(NOT(ISERROR(SEARCH(Foxtype,B2))),0),0),2))

It would be much shorter if you were using Excel 2007, rather than 2003, because we could use the IFERROR function, but if I've understood you right then that should work in 2003.

**jamieray** · 09-08-2011, 06:34 AM

Originally Posted by Andrew-R

If I've understood you right then:

Column B contains a list of product descriptions
Table contains two columns - the first is a product type (shoe, jean, pant, etc.), the second is some sort of description
Foxtype - is a named range consisting of just the first column of Table, which contains key words, which may match part of the description in column B.

If that's all right then the formula for cell B2 is =IF(ISERROR(MATCH(TRUE,INDEX(NOT(ISERROR(SEARCH(Foxtype,B2))),0),0)),"Missing from table",INDEX(Table,MATCH(TRUE,INDEX(NOT(ISERROR(SEARCH(Foxtype,B2))),0),0),2))

It would be much shorter if you were using Excel 2007, rather than 2003, because we could use the IFERROR function, but if I've understood you right then that should work in 2003.

One word - AMAZING!!

Thankyou very much!

I am actually using 2007 now and i havent updated my info on here. What would the shorter version be?

Many thanks for all your help

Jamie

**Andrew-R** · 09-08-2011, 06:44 AM

The shorter version uses IFERROR to return "Missing from table" if the lookup formula fails, rather than having to do the lookup twice (once to check if it works, once to return the value), so:

=IFERROR(INDEX(Table,MATCH(TRUE,INDEX(NOT(ISERROR(SEARCH(Foxtype,B2))),0),0),2),"Missing from table")