Look for data in a range - help

[Ainsley]

Can someone tell me which formula to use ?

I have a price list for a limited range of products. I have a cell in
which I display a product and want to look in my price list to see if
this product is listed, if it is I want to display the list price. If
its not, I want to say "not listed"

I can deal with the bit about looking up the price for the product, as
long as the product exists on the price list, but if the prduct is not
listed I fail.

My formula so far is : =VLOOKUP(D$12,UK!A$5:E$53,UK!C$1,FALSE)

This looks up the product code in D12 and checks in my price list 'UK'
then returns a result. So far Im happy with this, but how do I get it
to check if the product is actually on my price list in the first place
?

Either I get it to check the list first, or I get it to act upon a N/A
result, but Im not sure how to do either of these.

Hope this is clear (as mud), thanks

 

[Guest]

Hi

Try this:
=if(iserror(VLOOKUP(D$12,UK!A$5:E$53,UK!C$1,FALSE)),"not
listed",VLOOKUP(D$12,UK!A$5:E$53,UK!C$1,FALSE))

Andy.

 

[Ainsley]

Thanks, that works a treat

 

[Guest]

Thanks for the feedback!!

Andy.