Roger,
Thanks for responding.
I did read Help and other resources before I post so I was very much aware of the 1st paragraph of your post. I also agree that it is preferable to sort the table/array first to make it efficient. Biff gave me the headsup previously and thanks to Pete, I also read this link on optimal performance.
http://www.decisionmodels.com/optspeede.htm
So, you probably ask why I said this in the post.
"FALSE - no sorting required
TRUE - column where lookup_up value is in must be sorted in ascending order"
I was just trying to "paraphrase the rules" cited in Help. Maybe I did not do a good job.
Following is from Help.
********************************************************************************
a.. If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in **ascending order** ........
a.. If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be **in any order**.
a.. If TRUE or omitted .......The values in the first column of table_array must be placed in **ascending sort order**; otherwise, VLOOKUP may not give the correct value.
a.. If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array **do not need to be sorted**.
*********************************************************************************
I was trying to say the exact same thing in my post. So, I don't understand why it wasn't correct. Was it poor choice of words on my part?
If it is TRUE, we have no choice but to sort in ascending order first. If it is FALSE, we can get away without sorting although it is probably more efficient to sort first. This is my interpretation from reading Help etc. and I just wanted to lay out the "rules" in my own words in the post.
It is a very good idea that you said what you said so that others can see the whole picture. When I said "seeing the big picture" in one of my posts, I was referring to comparing VLOOKUP with Match. At one point, I made a mistake thinking that there was a difference between the two in terms of sorting. Later, I straightened out myself and requested others to ignore my prior posts on sorting and ended up with the last post.
I hope I haven't confused you or others more. This thread has helped me reinforce what I have learned so far. Thank you all for your support. Much appreciated.
Epinn
Hi
Not correct.
From Vlookup Help
"Range_lookup is a logical value that specifies whether you want
VLOOKUP to find an exact match or an approximate match. If TRUE or
omitted, an approximate match is returned. In other words, if an exact
match is not found, the next largest value that is less than
lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If
one is not found, the error value #N/A is returned"
Where possible, Sorted lookup tables are preferable, as all of the
functions Match, Vlookup and Hlookup will perform faster, but this has
no bearing upon whether you specify the True or False parameter, it all
depends upon the type of data and the result that you want.
--
Regards
Roger Govier
Please ignore my previous posts on sorting.
If I understand correctly, both VLOOKUP and MATCH require sorting in the
same manner.
FALSE - no sorting required
TRUE - column where lookup_up value is in must be sorted in ascending
order
I think I am okay now.
Epinn
Of course, I forgot about that. But for MATCH, it *never* requires
sorting, right?
See what I mean about learning the big picture. I think we all learn
better when we connect. Thanks for reminding me.
Cheers,
Epinn
Actually, if the last argument to vlookup is false (or 0) meaning you
want to
find an exact match, the lookup range does not need to be sorted.