5

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?

I cannot figure out what I am doing wrong there

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

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

1
  • That is what I missed, thank you for the answers! – Jonathan Nov 17 '17 at 12:38
8

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)

enter image description here

You could also use OFFSET which is often overlooked:

=OFFSET(A2,MATCH(C2,D2:D4,0)-1,0)
9
  • Thank you, I am also interested to know why is LOOKUP not returning the correct value. any Idea? – Jonathan Nov 17 '17 at 11:56
  • 1
    I'm interested to see how LOOKUP works myself now, I got the same results as you when I tried it, weird – PeterH Nov 17 '17 at 12:15
  • 1
    Apparently LOOKUP does a binary search with the only doc explaining that a small note in the syntax section... – ratchet freak Nov 17 '17 at 13:43
  • 1
    @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 – PeterH Nov 18 '17 at 4:30
  • 1
    @BruceWayne Cant remember exactly where I learnt it, but there are some simple steps in this link dummies.com/software/microsoft-office/excel/… – PeterH Nov 20 '17 at 8:09
2

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

5
  • 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'? – Jonathan Nov 17 '17 at 13:15
  • 1
    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 – PeterH Nov 17 '17 at 14:25
  • 1
    I understand now, LOOKUP then search for the closest maximum value to 5 and is 1. That is neat indeed, @User91504 thanks. – Jonathan Nov 17 '17 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. – Rajesh S Nov 18 '17 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,,. – Rajesh S Nov 18 '17 at 6:55
2

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.

1

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.

1
  • could you clarify this: what is re-sorting a table and do you have an example. – Jonathan Nov 18 '17 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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