Function for Lookup

[Steven Banks]

Hello Exceler's

I have a worksheet that I use to generate quotes for customers. I import a
file that gives me Product Number, Product Description, MSRP, and Cost.

Here's what happens;

I enter a part number like 98882-00, a product that retails for $259.95. If
it doesn't find it (or it doesn't exist)... it defaults to the next (closet)
number e.g., 98881-00 which retails for $139.95.

My Function looks like this;
=IF(ISNA(VLOOKUP(C19,Prices!A2:E26310,2)),"",VLOOKUP(C19,Prices!A226310,2)
)

What can condition can I add to make this formula tell me that the part does
not exist or needs to be added?

Any help deeply appreciated.
Thanks,
Steve Banks

 

[Steven Banks]

Steve,

Add "False" to your Vlookup to return an exact match. Then your formula
would

=IF(ISNA(VLOOKUP(C19,Prices!A2:E26310,2,False)),"Part Not
Available",VLOOKUP(C19,Prices!A226310,2,False)

Thank you Paul... this is perfect.

I appreciate your help! Enjoy your weekend.

Steve Banks

 

[Steven Banks]

You might try adding the FALSE option at the end of your VLOOKUP
formulas......

=IF(ISNA(VLOOKUP(C19,Prices!A2:E26310,2,FALSE)),"",VLOOKUP(C19,Prices!A226
310,2,FALSE)

then, if you wish, you could replace your "" with "PartNotFound".......


Vaya con Dios,
Chuck, CABGx3

Thank you Chuck... this is perfect. You and Paul helped me greatly. As this
problem ended up costing me some coinage!

I appreciate your help! Enjoy your weekend.

Steve Banks