# Issue with LOOKUP in excel

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;;;
``````

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

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

• That is what I missed, thank you for the answers! Nov 17, 2017 at 12:38

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)
``````
• Thank you, I am also interested to know why is `LOOKUP` not returning the correct value. any Idea? Nov 17, 2017 at 11:56
• I'm interested to see how LOOKUP works myself now, I got the same results as you when I tried it, weird Nov 17, 2017 at 12:15
• Apparently LOOKUP does a binary search with the only doc explaining that a small note in the syntax section... Nov 17, 2017 at 13:43
• @BruceWayne it's just macros I have saved in my personal work book and then added to the ribbon so I can use them in any workbook Nov 18, 2017 at 4:30
• @BruceWayne Cant remember exactly where I learnt it, but there are some simple steps in this link dummies.com/software/microsoft-office/excel/… Nov 20, 2017 at 8:09

@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.

• I tested and Formula 1 seems to work ignoring the sorting, so that is an interesting turn around. Do you think you can also explain how did you come to find that `1/(D317:D319 = C317)` is also a 'vector'? Nov 17, 2017 at 13:15
• i think it works by creating a vector of {#DIV/0 , #DIV/0 , 1} with only the 1 value being used then to lookup from the lookup vector. as it does not matter what you change the 5 to in this part =LOOKUP(5. overall very smart formula Nov 17, 2017 at 14:25
• I understand now, LOOKUP then search for the closest maximum value to 5 and is 1. That is neat indeed, @User91504 thanks. Nov 17, 2017 at 14:45
• Ultimately Computer searches the data in Binary format and justifies in terms of True or False. Vector format is, search in 1 Row or Column which finds the largest one,, if the Vector range is not been Sorted it may find Smallest then Lookup will return error #N/A. That's the reason Lookup has one more pattern called Array, which can search in more than a Column and Row. Nov 18, 2017 at 6:52
• Since your requirement was to repair the existing Formula otherwise I always prefer Index and Match combination. Anyway we all have solved the issue,,, finally a happy ending,,. Nov 18, 2017 at 6:55

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.

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.

• could you clarify this: what is re-sorting a table and do you have an example. Nov 18, 2017 at 20:16