VLOOKUP ERROR

[DeVoe]

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

 

[Toppers]

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.

 

[PCLIVE]

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

 

[PCLIVE]

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