Vertical Lookup from a List

[Guest]

I am attempting to have a variable reference to the table array in the
vlookup function. I have created a pull down list in A1 and want the results
of the pull down list to then reference the name of a corresponding table
array lookup. It seems that I have to either reference the name itself in
the formula and it is not able to reference the results of pull down menu A1.

Any help is appreciated!

 

[Biff]

How about some details........

Where is the lookup table (cell references)?

Do the selections from the drop down refer to different tables or the same
table but different sections?

How about some examples?

Biff

 

[Guest]

Got it...

Cell A1 is the pull down referring to a named list on a separate sheet. To
make it simple, say the list contains A, B, C & D, which also is the name of
the array that I want to call up. If I choose B, I want my formula to read
VLOOKUP(A4,A1,2) and then refer to named array B to get my answer.

Thanks for your reply.

 

[Biff]

Ok....

One way:

=VLOOKUP(A4,INDIRECT(A1),2)

Note: if the named range is dynamic the above won't work.

Biff

 

[Arvi Laanemets]

Hi

Got it...

Cell A1 is the pull down referring to a named list on a separate sheet.
To
make it simple, say the list contains A, B, C & D, which also is the name
of
the array that I want to call up. If I choose B, I want my formula to
read
VLOOKUP(A4,A1,2) and then refer to named array B to get my answer.

The syntax for VLOOKUP is :
VLOOKUP(LookupValue,LookupRange,ColumNumber,LookForNearest)

LookupValue is the value, the function is looking for in leftmost column of
LookupRange;
LookupRange is a contignous range, which must start with lookup column, and
contain return column;
Column number is the relative position of return column in LookupRange
(lookup column number is 1)
LookForNearest determines the behaviour of function when no exact match is
found.
When LookForNearest is False or 0, the exact match is searched for,
regardless of lookup column order. When no matching valie is found in
leftmost column, and #N/A error is returned;
When LookForNearest is True or 1, and the lookup table is sorted by
leftmost column, the nearest match for LookupValue is returned. When the
table is unordered, the function behaviour is not predictable;

VLOOKUP(A4,A1,2) doesn´t have any sinnvoll meaning and does return an error
(because lookup range is a singel cell, you are looking for a value A4 in
cell A1, and are attempting to return a value from second column of this
cell !!!).

OK. Let's quess what you did mean at all. You have some named ranges (A, B C
and D), with at least 2 columns in any of them, are you? Depending on
selection in data validation list in A1, you want to look for value in cell
A4 in leftmost column of one of those named ranges, and return according
value from second column of same range. Am I right so long?

On fly:
=VLOOKUP(A4,CHOOSE(MATCH(A1,{"A";"B";"C";"D"},0),A,B,C,D),2,0)

 

[Guest]

Both worked! Thanks!
--
BDG

Hi




The syntax for VLOOKUP is :
VLOOKUP(LookupValue,LookupRange,ColumNumber,LookForNearest)

LookupValue is the value, the function is looking for in leftmost column of
LookupRange;
LookupRange is a contignous range, which must start with lookup column, and
contain return column;
Column number is the relative position of return column in LookupRange
(lookup column number is 1)
LookForNearest determines the behaviour of function when no exact match is
found.
When LookForNearest is False or 0, the exact match is searched for,
regardless of lookup column order. When no matching valie is found in
leftmost column, and #N/A error is returned;
When LookForNearest is True or 1, and the lookup table is sorted by
leftmost column, the nearest match for LookupValue is returned. When the
table is unordered, the function behaviour is not predictable;

VLOOKUP(A4,A1,2) doesn´t have any sinnvoll meaning and does return an error
(because lookup range is a singel cell, you are looking for a value A4 in
cell A1, and are attempting to return a value from second column of this
cell !!!).

OK. Let's quess what you did mean at all. You have some named ranges (A, B C
and D), with at least 2 columns in any of them, are you? Depending on
selection in data validation list in A1, you want to look for value in cell
A4 in leftmost column of one of those named ranges, and return according
value from second column of same range. Am I right so long?

On fly:
=VLOOKUP(A4,CHOOSE(MATCH(A1,{"A";"B";"C";"D"},0),A,B,C,D),2,0)