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

Advertisements

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
 
Ad

Advertisements

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:D100));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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top