Using LOOKUP without values in ascending order

[thscc1659]

I'm trying to use the LOOKUP function to search a LOOKUP VECTOR that is not
in ascending order. Putting the values in ascending order is not an option.
Is there any way to use LOOKUP with these values?

Row B has Names and Row C has their corresponding race times in
chronological order. I'm trying generate a Top 10 times list (with the
corresponding names) from the data.

 

[Bob Bridges]

I never use LOOKUP, but the documentation seems to say that the list it's
searching must be in ascending order, so if that's not an option for you, you
must switch to MATCH or VLOOKUP (or HLOOKUP, of course, depending on the
direction of the vector). But why are you doing a lookup of any sort? Your
description of the task sounds more like what you want is to sort a list of
data by times and pick the top ten.

I know you said "Putting the values in ascending order is not an option",
but I'll bet it is. After all, you have the list of times, so you can
probably sort the list on that column. For example, do you mean only that
LEAVING them in ascending order is not an option? Perhaps you're just not
allowed to change the .xls file - but you can sort the list, pick out the top
ten times and names, and then just not save the file. Or maybe the .xls is
read-only to you - but you can copy the list to your own and unprotected
workbook and sort it there.

Whatever your answer to this, it isn't clear to me how a lookup function
will solve your problem anyway. A lookup expects you to have a particular
value you're looking for, say 3.53 seconds, which as I understand it is not
what you have available.

 

[T. Valko]

Row B has Names and Row C has their corresponding race times

I assume that's supposed to be column B has names and column C has times?

I'm trying generate a Top 10 times list

I'm assuming that the lowest times are the better times so you want the
names that correspond to the 10 lowest times?

Are there any duplicate times(ties)? If so, it gets kind of complicated and
it also depends on what your definition of a top 10 list includes. For
example, a top 10 list can have more than 10 items. Consider this example:

1
1
2
2
2
3
3

If you want the top 3 (lowest = best), how many comprise the top 3?

Names in the range B1:B100
Times in the range C1:C100

Enter this formula in E1 and copy down as needed:

=INDEX(B$1:B$100,MATCH(SMALL(C$1:C$100,ROWS(E$1:E1)),C$1:C$100,0))

If there are ties that fall within the smallest n you'll get incorrect
results for those ties.