# Vlookup

G

#### 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"

S

#### SteveH

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

HTH

Steve H

C

#### 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

G

#### 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;D1 100));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