VLOOKUP using a list not in alphabetical order.

[lukus2005]

I want to create a pulldown list to select various material based on
size (ie: 1/2" x 1", 5/8" x 1", 3/4" x 1", etc...). I then want to
use VLOOKUP to find the associated price.

The problem is that my list of material is not in alphabetical order
as i want list them based on size therefore, 5/8" before 3/4" or 1"
but Excel put the 1" first, followed by 1/2", 3/4", and then 5/8".

I thought about adding a column in my list of material to number the
items in the order i want them listed. But how do i tell VLOOKUP to
look at the "position" returned in my pulldown selection instead of
the actually "text" selected.

In other words, if i select 3/4" x 1" in my pulldown list, and say for
example, that selection is the 9th item in my pulldown, i don't want
VLOOKUP to look for 3/4" x 1" in my list price but instead, I want
VLOOKUP to look for the 9th item on my price list.

Is a Data Validation list capable of returning the position # of the
item selected in the list? If so, what is the function to do so?

TIA

 

[Pete_UK]

VLOOKUP has a 4th (optional) parameter which if missing defaults to
TRUE (or 1) meaning that the lookup table needs to be sorted. However,
if it is set to FALSE (or 0) then the table does not need to be
sorted, and VLOOKUP looks for an exact match. I think this is what you
need to use.

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

You already have your answer to the basic question. Just to add a comment -
if you are not using Data Validation for the drop down I would encourage you
to consider using it. Not that it puts the items in alphabetical order,
just because its easy to implement and quite nice.

Regarding another question you posed - the VLOOKUP can't return the position
of the item in a list unless you add a dummy column to your lookup table.
But that is exactly what the MATCH function is designed to handle. One
combines this function with INDEX, OFFSET, or INDIRECT to return an entry
based on position.

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[lukus2005]

VLOOKUP has a 4th (optional) parameter which if missing defaults to
TRUE (or 1) meaning that the lookup table needs to be sorted. However,
if it is set to FALSE (or 0) then the table does not need to be
sorted, and VLOOKUP looks for an exact match. I think this is what you
need to use.

Hope this helps.

Pete

Thanks, that works!