Issue with LOOKUP in excel

Question

I am currently meeting an issue with the LOOKUP function in excel

sometimes it would find the right data and sometimes not.

I made a picture example bellow, the two examples at the top gives the wrong results while the two at the bottom gives the right results.

Is my syntax or use of LOOKUP incorrect? How should I correct it to have the expected result?

here is the csv table of the data (my German computer uses ';' separators also in formula you may have to edit it )

;"LOOKUP(C2;D2:D4;A2:A4)";;;;;
D4;"=LOOKUP(C2;D2:D4;A2:A4)";3400;3400;;;
CI;;;2390;;;
F5;;;900;;;
;"LOOKUP(C6;D6:D11;A6:A11)";;;;;
D4;"=LOOKUP(C6;D6:D11;A6:A11)";3600;900;;;
F4;;;3600;;;
CI;;;2390;;;
D4;;;900;;;
B4;;;2250;;;
D4;;;900;;;
;;;;;;
;;;;;;
;"LOOKUP(C15;D15:D17;A15:A17)";;;;;
D4;"=LOOKUP(C15;D15:D17;A15:A17)";3600;900;;;
F4;;;3600;;;
CI;;;2390;;;
;;;;;;
;"LOOKUP(C20;D20:D27;A20:A27)";;;;;
D4;"=LOOKUP(C20;D20:D27;A20:A27)";3600;900;;;
F4;;;3600;;;
F6;;;3650;;;
F5;;;3700;;;
CI;;;2390;;;
D4;;;900;;;
B4;;;2250;;;
D4;;;900;;;

Answer 1

The reason your LOOKUP has failed is because the values in lookup_vector must be placed in ascending order:

..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;

otherwise, LOOKUP might not return the correct value

See link for more info:

https://support.office.com/en-gb/article/LOOKUP-function-446d94af-663b-451d-8251-369d5e3864cb

Answer 2

If I have understood what you are trying to do, I would suggest using INDEX and MATCH:

See my example which you could use in your first example:

=INDEX(A2:A4,MATCH(C2,D2:D4,0),1)

You could also use OFFSET which is often overlooked:

=OFFSET(A2,MATCH(C2,D2:D4,0)-1,0)

Answer 3

@Jonathan, I would like to suggest TWO Formula.

Formula 1 is,, =LOOKUP(5,1/(D317:D319 = C317),A317:A319)

Formula 2 is,, =LOOKUP($C$317,D317:D$339,A317:A$339)

NB: Data Range is A317 to D317 for formula 1.

NB: Data Range is A317 to D339 for formula 2.

For first Formula.

Lookup Value in C317

Lookup Range is D317 to D319.

Result Vector Range is A317 to A319.

NB: You need to write Formula for each Data Range.

Note also for Formula 1, instead of 5 any value should be used which is Greater than 1. Use of 5/1 to find True & False.

Other precaution is Data Range should be Sorted in Ascending Order.

Also, for Formula 2 I've used entire Data Range because of Blank rows only.

Hope this help you, I've posted the solution after is been tested by me. I can upload the screen Shot if & when you need.

Answer 4

From the Office Support Website:

Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

This is made clear in the VLOOKUP function, that officially supersedes LOOKUP, where there is a last parameter that chooses between mimicking LOOKUP's behavior (sorted data required) or searches through unsorted data.

Answer 5

I've found that if I re-sort the table, even though everything appears to be in order, LOOKUP will then return the correct answer.