Lookup Function Giving Inconsistent Results in Excel & Google Spreadsheet

Question

I am trying to create a column of looked-up values using the LOOKUP function. The goal is for the formula to look up the "rank" value of each "name" and print it in the "lookup" cell on the same row.

I can get it to work very easily on a small scale, but as soon as I try to make it work elsewhere it stops working correctly, giving me weird results.

Behaves as expected on small scale:

Doesn't behave as expected on larger scale:

Can someone tell me why I'm getting inconsistent results from the LOOKUP method? I've tried everything I can think of to fix this, including making a new spreadsheet from scratch and even switching between spreadsheet programs. I am consistently getting the same incorrect results, so there must be something wrong with my understanding of the LOOKUP formula.

Any help at all would be appreciated. Thank you very much!

Answer 1

LOOKUP requires the lookup column to be sorted ascending. Use VLOOKUP instead:

=VLOOKUP(A19,A:B,2,FALSE)

The FALSE now looks for an exact match.

Answer 2

Lookup vector that includes the lookup values should be sorted. Here is Microsoft's guide for the function: LOOKUP.

I suggest the INDEX & MATCH combination though. Such as:

=INDEX($I$19:$I$30,MATCH(A19,$A$16:$A$30,0),1)

Here is a guide for dynamic-duo of Excel: INDEX & MATCH: A Better Way to Look Up Data