Hi
B Golden said:
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)