Function for Lookup

  • Thread starter Thread starter Steven Banks
  • Start date Start date
S

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!A2:D26310,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
 
Paul Corrado said:
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!A2:D26310,2,False)
Click to expand...

Thank you Paul... this is perfect.

I appreciate your help! Enjoy your weekend.

Steve Banks
 
CLR said:
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!A2:D26
310,2,FALSE)

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


Vaya con Dios,
Chuck, CABGx3
Click to expand...

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
 
Back
Top