I have a table describing results in a race series.
The sort order for the table is in a column containing the total points scored in the races so far by each driver. Demonstrably this can change race by race.
In another part of the sheet I wish to add the scores for drivers in the same team, so I am trying to locate the row containing a specified driver number in column A, but this column will unavoidably be in a largely random order, which I cannot do in either LOOKUP and MATCH, both of which depend on the lookup array being sorted ascending.
Is there any way to tell either function that the lookup array is in random order, or is there another function that can locate values in a random order array?
MATCH or VLOOKUP (HLOOKUP) used for exact matches (or first match) don’t need sorted arrays. Set the respective last parameter to 0.
In an Answer @Jotra had written this comment: (Answer deleted).
I cannot use either VLOOKUP or HLOOKUP because the cell ranges cannot be adjacent, however, I now understand why the value returned by MATCH is radically different to what I was expecting. What I am getting is the index number into the array, which leads me to a different solution.