Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
Can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
can a formula check for a certain value in a range? | Excel Discussion (Misc queries) |