Search code examples
excelindexingexcel-formulaexcel-2007worksheet-function

LOOKUP function giving strange result

Using LOOKUP in Excel I want to show the cell that has biggest score but the result is strange.

Example: my Excel table:

example : my excel table

From that table I want to determine who got the biggest score, and I use this code: 

for biggest score no 1 "=LOOKUP(MAX(C4:C8),C4:C8,B4:B8)"
for biggest score no 2 "=LOOKUP(LARGE(C4:C8,2),C4:C8,B4:B8)"
for biggest score no 3 "=LOOKUP(LARGE(C4:C8,3),C4:C8,B4:B8) "

an the result is strange: 

for biggest score no 1 it show "mordi"
for biggest score no 2 it show "mordi"
for biggest score no 3 it show "ngun"

Can anyone correct me and am I using the wrong function?

Solution

  • What your first formula is doing is finding 5 in C4:C8 but then looking for that 5 in a list that is not in ascending order. The search is binary - in effect it compares 5 with the mid point 3 so then forgets about 5 and 4 because it expects 5 to be after 3 so continues the search only in the second half of the range (again splitting it into two). It does not find it there so defaults (perhaps not really intentionally) to the last entry (ie 1) then returns the corresponding value in Column B for that.

    I suggest one formula for all three 'ranks', with the 'rank' specified outside the formula, say 1 in G4, 2 in G5 and 3 in G6. Then apply: 

    =INDEX(B$4:B$8,MATCH(LARGE(C$4:C$8,G4),C$4:C$8,0))

    This should return johan and when copied down aan and then ngun.

    It uses LARGE, as you have been, but inside a MATCH function to find the relative position of the required score in ColumnC and then returns the content of that same relative position in the range in ColumnB.

    VLOOKUP would not suit because the result is to the left in the table_array.

    It might be easier to see what is happening if the scores where not the 'ranks' in reverse.