Insert Vlookup into table_array of Vlookup with named range

[Denise]

It appears I can type a named range in table_array, but I cannot either
1) link to a cell containg the named range. or (#N/A)
2) use a nested Vlookup to return the name of the range.(#VALUE!)

When I nest a VLookup statenent in place of table-array within another
VLookup statement, I receive the error #VALUE!. The value to be returned from
the lookup is the name of the range that the primary Vlookup should use to
find the lookup_value.

I was able to nest a VLookup statmenent for col_index_number within another
VLookup statement successfully.
This is the statement:
=VLOOKUP(D2,(VLOOKUP(B2,capability,2,FALSE)),VLOOKUP(A2,equip,3,FALSE),FALSE)

The second nested Vlookup works, probably because it is returning a numeric
column number value from the "equip" range.
The first nested Vlookup does not work. It looks to the range "capability"
to return the name of the range for table_array.
When placed in its own cell, this formula correctly returns the text name of
the range "speed". When I replace the formula with the test "speed", the
lookup value also works. =VLOOKUP(B2,capability,2,FALSE)
However, if I link the primary lookup to the cell containing this Vlookup,
properly displaying "speed", the primary Vlookup returns the error #N/A.
If I simply type the word "speed" in the cell, and refer to it, this also
does not work and returns the error #N/A.

 

[Peo Sjoblom]

You can retrieve the name using lookup or match and index but you need to
wrap the textstring in INDIRECT to actually get the contents of the tables,
for instance if MyTable returns names of ranges that you want to lookup with
another lookup you can use

=VLOOKUP(B1,INDIRECT(VLOOKUP(A1,MyTable,2,0)),2,0)


Regards,

Peo Sjoblom