Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ERROR
I'm trying to setup a product catalog that will allow a seperate worksheet to
perform a VLOOKUP of a product number to retrieve the product description and price. What I'm finding is that if I create my catalog in a structured way (ex: Home Software, Work Software and Server Software) and then give each product its appropriate catalog number (ex: 100, 200, 300) in a linear format (lowest number first), then everything works great! However, as soon as I add another product to the list that falls out of numeric order, the entire VLOOKUP fails because it thinks that the number doesn't exist in the column range that it is performing the lookup. For example: (3 column sheet: Product Number, Product Description, Product Price) 100 Software One $1.00 200 Software Two $2.00 150 Software Three $3.00 When the main worksheet performs a VLOOKUP for the value of 150, the sheet returns NO values. This, as I've been able to tell happens because the program had already passed the value of 100 and then found the value of 200 thus stopping any further lookup for the value of 150 because it wasn't found between the numbers of 100 and 200. But if I put that 150 value and put it between the other two numbers, then my VLOOKUP performs correctly. My problems is that I want to create an organized catalog by categories as most catalogs are created. But in doing that, the numbers will never be in chronilogical order from lowest to highest. So, is this a BUG in the software or am I missing something that would prevent my VLOOKUP from doing a COMPLETE lookup through the entire cell range that I have defined for the formula? I appreciate any immediate assistance to my problem. Thanks, Doug |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ERROR
Try:
=VLOOKUP(150,$A:$C,2,0) =VLOOKUP(150,$A:$C,3,0) Change 150 to your cell address as required If you specify the last parameter as FALSE (or 0) then VLOOKUP does not require the data to be sorted. "DeVoe" wrote: I'm trying to setup a product catalog that will allow a seperate worksheet to perform a VLOOKUP of a product number to retrieve the product description and price. What I'm finding is that if I create my catalog in a structured way (ex: Home Software, Work Software and Server Software) and then give each product its appropriate catalog number (ex: 100, 200, 300) in a linear format (lowest number first), then everything works great! However, as soon as I add another product to the list that falls out of numeric order, the entire VLOOKUP fails because it thinks that the number doesn't exist in the column range that it is performing the lookup. For example: (3 column sheet: Product Number, Product Description, Product Price) 100 Software One $1.00 200 Software Two $2.00 150 Software Three $3.00 When the main worksheet performs a VLOOKUP for the value of 150, the sheet returns NO values. This, as I've been able to tell happens because the program had already passed the value of 100 and then found the value of 200 thus stopping any further lookup for the value of 150 because it wasn't found between the numbers of 100 and 200. But if I put that 150 value and put it between the other two numbers, then my VLOOKUP performs correctly. My problems is that I want to create an organized catalog by categories as most catalogs are created. But in doing that, the numbers will never be in chronilogical order from lowest to highest. So, is this a BUG in the software or am I missing something that would prevent my VLOOKUP from doing a COMPLETE lookup through the entire cell range that I have defined for the formula? I appreciate any immediate assistance to my problem. Thanks, Doug |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ERROR
In the explanation for VLOOKUP, it states that your lookup table must be in
ascending order. If you don't want to keep your table in ascending order, then you'll have to use a different function (match, index, etc.). =INDEX(A1:C3,MATCH(F1,A1:A3,0),3) This is with using columns A, B, & C. I've used F1 as the lookup value (ie.- 150). HTH, Paul "DeVoe" wrote in message ... I'm trying to setup a product catalog that will allow a seperate worksheet to perform a VLOOKUP of a product number to retrieve the product description and price. What I'm finding is that if I create my catalog in a structured way (ex: Home Software, Work Software and Server Software) and then give each product its appropriate catalog number (ex: 100, 200, 300) in a linear format (lowest number first), then everything works great! However, as soon as I add another product to the list that falls out of numeric order, the entire VLOOKUP fails because it thinks that the number doesn't exist in the column range that it is performing the lookup. For example: (3 column sheet: Product Number, Product Description, Product Price) 100 Software One $1.00 200 Software Two $2.00 150 Software Three $3.00 When the main worksheet performs a VLOOKUP for the value of 150, the sheet returns NO values. This, as I've been able to tell happens because the program had already passed the value of 100 and then found the value of 200 thus stopping any further lookup for the value of 150 because it wasn't found between the numbers of 100 and 200. But if I put that 150 value and put it between the other two numbers, then my VLOOKUP performs correctly. My problems is that I want to create an organized catalog by categories as most catalogs are created. But in doing that, the numbers will never be in chronilogical order from lowest to highest. So, is this a BUG in the software or am I missing something that would prevent my VLOOKUP from doing a COMPLETE lookup through the entire cell range that I have defined for the formula? I appreciate any immediate assistance to my problem. Thanks, Doug |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ERROR
An additional though:
There are probably other ways to do this, but if the lookup value doesn't exist, then the #N/A will show up. To prevent that, you can use: =IF(ISERROR(INDEX(A1:C3,MATCH(F1,A1:A3,0),3)),"No Match",INDEX(A1:C3,MATCH(F1,A1:A3,0),3)) "PCLIVE" wrote in message ... In the explanation for VLOOKUP, it states that your lookup table must be in ascending order. If you don't want to keep your table in ascending order, then you'll have to use a different function (match, index, etc.). =INDEX(A1:C3,MATCH(F1,A1:A3,0),3) This is with using columns A, B, & C. I've used F1 as the lookup value (ie.- 150). HTH, Paul "DeVoe" wrote in message ... I'm trying to setup a product catalog that will allow a seperate worksheet to perform a VLOOKUP of a product number to retrieve the product description and price. What I'm finding is that if I create my catalog in a structured way (ex: Home Software, Work Software and Server Software) and then give each product its appropriate catalog number (ex: 100, 200, 300) in a linear format (lowest number first), then everything works great! However, as soon as I add another product to the list that falls out of numeric order, the entire VLOOKUP fails because it thinks that the number doesn't exist in the column range that it is performing the lookup. For example: (3 column sheet: Product Number, Product Description, Product Price) 100 Software One $1.00 200 Software Two $2.00 150 Software Three $3.00 When the main worksheet performs a VLOOKUP for the value of 150, the sheet returns NO values. This, as I've been able to tell happens because the program had already passed the value of 100 and then found the value of 200 thus stopping any further lookup for the value of 150 because it wasn't found between the numbers of 100 and 200. But if I put that 150 value and put it between the other two numbers, then my VLOOKUP performs correctly. My problems is that I want to create an organized catalog by categories as most catalogs are created. But in doing that, the numbers will never be in chronilogical order from lowest to highest. So, is this a BUG in the software or am I missing something that would prevent my VLOOKUP from doing a COMPLETE lookup through the entire cell range that I have defined for the formula? I appreciate any immediate assistance to my problem. Thanks, Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup value not available error | Excel Discussion (Misc queries) | |||
vlookup #N/A error | Excel Discussion (Misc queries) | |||
REF# error using VLOOKUP | Excel Worksheet Functions | |||
vlookup error!! | Excel Worksheet Functions | |||
#N/A error with VLOOKUP | Excel Discussion (Misc queries) |