vlookup question if item isn't in table

G

Guest

How can I change a VLOOKUP formula to provide a set response if the item
being searched for in the VLOOKUP is not in the table?

I have a formula that looks up a part number entered into column B and fills
in column F with data if the part number entered in column B is in my table.

=IF(B64="","",VLOOKUP(B64,ProductLookup,8,FALSE))

If B64 isn't in the ProductLookup table, I want the column F to default to
Hdwr/Sftwr rather than #N/A.

Thanks,

Nancy
 
B

Bob Umlas

Excel 2003 & earlier:
=IF(B64="","",IF(ISNA(VLOOKUP(B64,ProductLookup,8,FALSE)),"Hdwr/Sftwr",VLOOKUP(B64,ProductLookup,8,FALSE)))
in Excel 2007:
=IF(B64="","",IFERROR(VLOOKUP(B64,ProductLookup,8,FALSE)),"Hdwr/Sftwr")
 
B

Bernard Liengme

=IF(B64="","",IF(ISNA(VLOOKUP(B64,ProductLookup,8,FALSE)),"Hdwr/Sftwr",VLOOKUP(B64,ProductLookup,8,FALSE)))

best wishes
 
G

Guest

One way ..

Use:
=IF(B64="","",IF(ISNA(VLOOKUP(B64,ProductLookup,8,0)),"Hdwr/Sftwr",VLOOKUP(B64,ProductLookup,8,0)))
 
G

Guest

Thank you so much! I actually run 2007, however others run 2003 so I'm
gratefull you provided both ways.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLookup - can I set my formula to fill in date if item isn't in ta 8
Excel IF statement with vlookup 2
Excel Vlookup Help 0
EXCEL - IF(IFERROR(VLOOKUP question 0
vlookup challenge with hyphen data placement 2
VLOOKUP isn't working properly in Excel on Mac 1
VLOOKUP returning #N/A result 2
VLOOKUP TABLE ERROR 6

Top