Vlookup

[Guest]

Hi,
i want to do a vlookup, being my lookup value from a validation list.
The answer is #N/A!
Is there a resrtition to the use of this formula with validation date?

heres the example in A1:

=vlookup(b1;example;1;false), where b1 is a validated by the list
"x100:x105" and "example=w100:x105"

 

[SteveH]

The lookup values need to be in the first column of the array, not the last.

HTH

Steve H

 

[CLR]

Your formula looks fine.........probably the reason you're getting the #N/A
is because Excel is not finding your value in the lookup table........could
be one is TEXT vs the other being NUMBERS..........

From HELP:
Remarks

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
largest value that is less than or equal to lookup_value.


If lookup_value is smaller than the smallest value in the first column of
table_array, VLOOKUP returns the #N/A error value.


If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
returns the #N/A value.

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

You want to make left VLOOKUP
Try :
=VLOOKUP(B1;CHOOSE({2;1};W100:W105;X100:X105);2;FALSE)

Formula =CHOOSE({1;2;3;….};col1;col2;col3;…) return an array.
So, you can make VLOOKUP between columns where ever they are, even in
different sheets or books.
For example:
vlookup between 2 columns (columns no in same array):
=VLOOKUP(xxxx;CHOOSE({1;2};A1:A100;D1100));2;0)

Left vlookup:
=VLOOKUP(xxxx;CHOOSE({2;1};A1:A100;B1:B100));2;0)

vlookup between column and row:
{=VLOOKUP(xxxx;CHOOSE({1;2};A4:A13;TRANSPOSE(A2:J2));2;0)}


vlookup between 2 books:
{=VLOOKUP(D1;CHOOSE({1;2};
'C:\examples\[book1.xls]Sheet1'!$A1:$A2000;
'C:\examples\[book2.xls]Sheet1'!$C100:$C2099);2;FALSE)}

Ioannis Varlamis, Athens