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

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

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

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

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

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