Why is This?

[John]

I have the following lookup, which doesn't return by expected value

=IF(L6="","",VLOOKUP(L6,Masters!A1:B23,2))


L6 = Drop down list from a Range name "Stores"
Masters A = The Range Name "Stores"
Masters B = The values I want returned

It only seems to work if Column A is sort alphabetically, is that correct? I
didn't think this had to be the case


Thanks

 

[Bob Phillips]

=IF(L6="","",VLOOKUP(L6,Masters!A1:B23,2,False))

should work regardless

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[John]

Thanks Bob, it does now with the "False" appended

 

[Arvi Laanemets]

Hi

About "Why":

VLOOKUP with 4th parameter TRUE or 1 returns nearest match. Data in lookup
range must be sorted on leftmost column - otherwise the result is
unpredictable.

VLOOKUP with 4th parameter FALSE or 0 returns first exact match. Data in
lookup range can be unsorted. When there is no exact match, the function
returns error - because this mostly it will be clever to use error trapping.
=IF(ISERROR(VLOOKUP(...)),"",VLOOKUP(...))


Arvi Laanemets

 

[John]

Thanks for the explanation Arvi

Hi

About "Why":

VLOOKUP with 4th parameter TRUE or 1 returns nearest match. Data in lookup
range must be sorted on leftmost column - otherwise the result is
unpredictable.

VLOOKUP with 4th parameter FALSE or 0 returns first exact match. Data in
lookup range can be unsorted. When there is no exact match, the function
returns error - because this mostly it will be clever to use error
trapping.
=IF(ISERROR(VLOOKUP(...)),"",VLOOKUP(...))


Arvi Laanemets