Look for data in a range - help
 

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 :)


--
Ainsley
------------------------------------------------------------------------
Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960
View this thread: http://www.excelforum.com/showthread...hreadid=566969

Look for data in a range - help
 

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" wrote in
message ...

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 :)


--
Ainsley
------------------------------------------------------------------------
Ainsley's Profile:
http://www.excelforum.com/member.php...o&userid=31960
View this thread: http://www.excelforum.com/showthread...hreadid=566969

Look for data in a range - help
 

Thanks, that works a treat.


--
Ainsley
------------------------------------------------------------------------
Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960
View this thread: http://www.excelforum.com/showthread...hreadid=566969

Look for data in a range - help
 

Thanks for the feedback!!

Andy.

"Ainsley" wrote in
message ...

Thanks, that works a treat.


--
Ainsley
------------------------------------------------------------------------
Ainsley's Profile:
http://www.excelforum.com/member.php...o&userid=31960
View this thread: http://www.excelforum.com/showthread...hreadid=566969