Is sorting the data in an ascending order necessary while populating vlookup (doubt, not a problem)

[ThrottleWorks]

Hi,

I have a doubt about vlookup. The excel help for vlookup suggests that “the data must be sorted in an ascending order”.

I could not understand this condition cause till the date I have never thought about sorting the data & always got correct results.

To check this, I tried multiple vlookups with same set of data, but the results seems to be correct.

Could anyone please guide me on this.
I am not facing any issue cause of this, but just looking for clearing my doubt.

P.S. - Moderators, please delete the post if necessary.

 

[Abhijeet R. Joshi]

Hi Sachin,

Even I never used the sorting part, but since the vlookup considers only the first matching value and provides you with the same this will be the reason for using the sort...Saying your looking for "A" and in the other range you have 1000 and 2000 corresponding to "A" so if you sort smallest to largest you will get 1000 and vice versa..
I am sure you will get better explained answers for this from the other folks...

 

[shrivallabha]

VLOOKUP's last argument is quite interesting part which can be set to 0 or FALSE[exact match] and 1 or TRUE [approximate match].

Sorting does not matter when you are setting this argument to 0 or FALSE. However, when you are using 1 or TRUE the data MUST be in the ascending order to get correct results.

Go through this link written by Colin Legg:
http://colinlegg.wordpress.com/2012/03/25/binary-searches-with-vlookup/

 

[ThrottleWorks]

Hi Shrivallabha, thanks for the link, this is not accessible from the office, will check at home.

Sorting does not matter when you are setting this argument to 0 or FALSE

This is where I got confused, I hardly use 1 or TRUE in my vlookups.
That is why I thougth sorting is not required to get correct results.

Thanks a lot for the explanation.