VLookUp Tables

[Louise]

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

 

[JulieD]

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

 

[Niek Otten]

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]

That's confirmed it then! Thanks very much.

Louise

 

[JulieD]

you're welcome

That's confirmed it then! Thanks very much.

Louise

 

[Bob Phillips]

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)