Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLookUp Tables
hi all
Quick question - or at least I Hope it is!! When using a VLookUp table, does the lookup table itself have to be sorted by the left-hand column in order for the formulas to work? I thought it did, however, one of my colleagues informs me that if you put 'FALSE' as the last value in the lookup, the table doesn't have to be sorted at all. Hope this makes sense. Any help would be appreciated. Thank you. Louise |
#2
|
|||
|
|||
Hi Louise
your colleague is correct, if you use the fourth parameter of FALSE or 0 then the VLOOKUP table does NOT have to be sorted ascending on the left-most column and you're looking for an EXACT match. If you put TRUE, 1 or leave the fourth parameter Blank then you're looking for an APPROXIMATE match and to get an anywhere near sensible answer the VLOOKUP table does need to be sorted Cheers JulieD "Louise" wrote in message ... hi all Quick question - or at least I Hope it is!! When using a VLookUp table, does the lookup table itself have to be sorted by the left-hand column in order for the formulas to work? I thought it did, however, one of my colleagues informs me that if you put 'FALSE' as the last value in the lookup, the table doesn't have to be sorted at all. Hope this makes sense. Any help would be appreciated. Thank you. Louise |
#3
|
|||
|
|||
Hi Louise,
Your colleague is right, as can easily be checked in HELP. This way Excel cannot select a near value if no exact match is found, of course. For long tables, it is a slow function, because it looks through the table serially instead of using clever search methods that can be applied to sorted tables. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Louise" wrote in message ... hi all Quick question - or at least I Hope it is!! When using a VLookUp table, does the lookup table itself have to be sorted by the left-hand column in order for the formulas to work? I thought it did, however, one of my colleagues informs me that if you put 'FALSE' as the last value in the lookup, the table doesn't have to be sorted at all. Hope this makes sense. Any help would be appreciated. Thank you. Louise |
#4
|
|||
|
|||
That's confirmed it then! Thanks very much.
Louise "JulieD" wrote: Hi Louise your colleague is correct, if you use the fourth parameter of FALSE or 0 then the VLOOKUP table does NOT have to be sorted ascending on the left-most column and you're looking for an EXACT match. If you put TRUE, 1 or leave the fourth parameter Blank then you're looking for an APPROXIMATE match and to get an anywhere near sensible answer the VLOOKUP table does need to be sorted Cheers JulieD "Louise" wrote in message ... hi all Quick question - or at least I Hope it is!! When using a VLookUp table, does the lookup table itself have to be sorted by the left-hand column in order for the formulas to work? I thought it did, however, one of my colleagues informs me that if you put 'FALSE' as the last value in the lookup, the table doesn't have to be sorted at all. Hope this makes sense. Any help would be appreciated. Thank you. Louise |
#5
|
|||
|
|||
you're welcome
"Louise" wrote in message ... That's confirmed it then! Thanks very much. Louise "JulieD" wrote: Hi Louise your colleague is correct, if you use the fourth parameter of FALSE or 0 then the VLOOKUP table does NOT have to be sorted ascending on the left-most column and you're looking for an EXACT match. If you put TRUE, 1 or leave the fourth parameter Blank then you're looking for an APPROXIMATE match and to get an anywhere near sensible answer the VLOOKUP table does need to be sorted Cheers JulieD "Louise" wrote in message ... hi all Quick question - or at least I Hope it is!! When using a VLookUp table, does the lookup table itself have to be sorted by the left-hand column in order for the formulas to work? I thought it did, however, one of my colleagues informs me that if you put 'FALSE' as the last value in the lookup, the table doesn't have to be sorted at all. Hope this makes sense. Any help would be appreciated. Thank you. Louise |
#6
|
|||
|
|||
The False will look for an exact match, regardless of whether it is sorted
or not. True will look for the next highest value which requires the table to be sorted. If you add False, a mis-match will return #N/A, which you can test for with =IF(ISNA(vlookup_formula),"",vlookup_formula) -- HTH RP (remove nothere from the email address if mailing direct) "Louise" wrote in message ... hi all Quick question - or at least I Hope it is!! When using a VLookUp table, does the lookup table itself have to be sorted by the left-hand column in order for the formulas to work? I thought it did, however, one of my colleagues informs me that if you put 'FALSE' as the last value in the lookup, the table doesn't have to be sorted at all. Hope this makes sense. Any help would be appreciated. Thank you. Louise |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
How to paste Xcel tables legibly into Word . Help!! | Excel Discussion (Misc queries) | |||
VLOOKUP not working | Excel Worksheet Functions |