Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using LOOKUP without values in ascending order
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using LOOKUP without values in ascending order
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. --- "thscc1659" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using LOOKUP without values in ascending order
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. -- Biff Microsoft Excel MVP "thscc1659" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function Not in Ascending Order | Excel Worksheet Functions | |||
eliminate repeat values from a list of ascending order | Excel Worksheet Functions | |||
How do I # my rows in ascending order. | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions | |||
Graph values in ascending order | Excel Discussion (Misc queries) |