VLookup doesn't pick up the correct values

[GN0001]

I did a VLookUp at work today. it picks up the value from the bottom of the list for the first look up value. What is the reason?

I sorted, it did work properly, but I need to know what is the issue when it is not sorted. it doesn't pick up value.

Do we always need to sort the the look up list and the list from which our look up value is looked on?

I don't always sort and it picks up the correct value.

Please advise me.
GN0001

 

[Somendra Misra]

Hi GN0001,

You must have selected TRUE as your fourth argument of VLOOKUP(), which does an approximate match which works on sorted table. For unsorted table use FALSE or 0 for an exact match.

Regards,

 

[NARAYANK991]

Hi ,

The VLOOKUP function has two versions :

=VLOOKUP(lookup value , lookup range , column number , TRUE)

=VLOOKUP(lookup value , lookup range , column number , FALSE)

When the second version is used , the lookup range does not need to be sorted ; when the first version is used , it has to be.

When the second version is used , if the lookup value is not found in the lookup range , the function will return the #N/A error value ; in the first version , if the lookup range is sorted , the function will return the nearest value in the range.

Play around with the file , and see how the return value differs.

Narayan

 

[Deepak]

You may use INDEX+MATCH in place of VLOOKUP. Follow the below link for detailed illustration of the same.
INDEX+MATCH is far better than VLOOKUP.

http://www.contextures.com/xlFunctions03.html

 

[ThrottleWorks]

Hi,

I had similar doubt. Please check the below link. Might be helpful.

http://forum.chandoo.org/threads/is...vlookup-doubt-not-a-problem.13649/#post-80587

 

[GN0001]

Hi Guity ,

The VLOOKUP function has two versions :

=VLOOKUP(lookup value , lookup range , column number , TRUE)

=VLOOKUP(lookup value , lookup range , column number , FALSE)

When the second version is used , the lookup range does not need to be sorted ; when the first version is used , it has to be.

When the second version is used , if the lookup value is not found in the lookup range , the function will return the #N/A error value ; in the first version , if the lookup range is sorted , the function will return the nearest value in the range.

Play around with the file , and see how the return value differs.

Narayan

------------------------------------------------------------
Narayan,
No, I entered my function as:
VLOOKUP(lookup value , lookup range , column number , FALSE)
I am sure about it, but it brought me back the list from bottom to top. very strange.

 

[GN0001]

Hi GN0001,

You must have selected TRUE as your fourth argument of VLOOKUP(), which does an approximate match which works on sorted table. For unsorted table use FALSE or 0 for an exact match.

Regards

Hi,
I had False and I am sure about it.
GN0001

 

[Somendra Misra]

Consider, uploading you file (if possible) so that we can look at the issue.

Regards,

 

[GN0001]

You may use INDEX+MATCH in place of VLOOKUP. Follow the below link for detailed illustration of the same.
INDEX+MATCH is far better than VLOOKUP.

http://www.contextures.com/xlFunctions03.html

But I want to find out the reason why VLookUp acting as this, instead or replacing with another function.
Thanks,
GN0001

 

[Haseeb A]

Hello GN0001,

Could you please attach a sample file? without seeing the actual file , it is hard to tell what is the issue. I am sure should have something even if you have used FALSE. If you have multiple lookup_values in the range, above one may have spaces or invisible characters

 

[GN0001]

Consider, uploading you file (if possible) so that we can look at the issue.

Regards,

I already sorted the column and it worked for me. So I don't have it, but I try to go back to start position when I was experiencing the issue.
GN0001