0

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:

Works as expected

Doesn't behave as expected on larger scale:

Doesn't work as expected

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!

|improve this question
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.

|improve this answer
0

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

|improve this answer

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.