lookup unsorted list

[Guest]

Hi,

In column A I have a list of product items. In column B I wish to place a
rank on a few (not all) of these items, and not in an ascending order from
the top of the page. Using this data, I wish to automatically take the ranked
items from this table and place in their correct order in another table.

ie
Column A Column B
Trudi 3
Simon
Anton 4
Stacey 1
Renee
Karen 2

The Result I am aiming for in a different part of the spreadsheet, while
leaving this information alone, is
Stacey 1
Karen 2
Trudi 3
Anton 4

I thought I could use the lookup function, but this requires the list to be
sorted in an ascending order, something that I am unable to do.

Is anyone able to provide a neat solution for this problem.

Thanks

 

[Nick Hodge]

Not sure I understand, but I suspect you are not using the FALSE parameter
as the last one in you VLOOKUP or HLOOKUP. With this, a lookup does not
need to be sorted I any order although any data not found will return an
#N/A error. This can be overcome by wrapping the lookup in the ISNA or
ISERROR function however or you could just live with the error.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Guest]

Just curious, why do you say you can't sort your original table in ascending/
descending order? Is it coz you don't want to disturb the original list? If
not, there is actually a way to sort it. Let me know if you need to know how.