Excel VLOOKUP show wrong results

1
votes

I try to preprocess some excel documents with VLOOKUP function.

Disclaimer: This is not real data that I need to work on, but some demo data with same problem! enter image description here

The vlookup function for left columns is 

=VLOOKUP(B7;$D$6:$E$10;2;1)

and for right one is

=VLOOKUP(K11;$M$8:$N$13;2;1)

( of course, the first number is changing, B7, B8,...K11, K12, ....)

What is wrong:

On the left side, there appear as there is no key, but is is.

On the right side for lidija, the value should be j and not y.

I couldn't find what is wrong even though I search on the net quit a lot.

Some thinks that (it is obvious from picture and description) I already check:

(and it is stated as most frequent problems)

  1. I lock the cells (see code above)

  2. The key exist - you can see the key in table, and I do it with copy and paste

  3. It is not a space problem (I check that none of the cells have space at the end)

1
excelvlookup
What is "Left side" and what is "Right side" here? Very confusing write up. - JNevill
Also... not understanding what is going on here.... that last parameter of your vlookup is a True or False. 1 being True you are doing a "Range Lookup". Perhaps change that last parameter to False and see if your problem (whatever it is) goes away. - JNevill
left side is starting on A7, right side is starting on j11. Yes 1 is True, 0 is False - Marko Zadravec
....you are looking for an exact match in unsorted data...so you need zero or FALSE as 4th argument - see my answer - barry houdini

1 Answers

7
votes

You are using 1 as 4th argument. This is only appropriate if the lookup range is sorted and you want a "closest match"

In unsorted data, as here, for an exact match you need a 4th argument of zero or FALSE like this

=VLOOKUP(B7;$D$6:$E$10;2;FALSE)