referencing a cell determined by name in another cell

  • Thread starter ricardo wants answers
  • Start date
R

ricardo wants answers

How do I reference a cell within a formula based on the contents of another
cell?
For example if one cell contained a reference/address for a column array,
how could I refer to this cell (and hence the column array) within the
vlookup function instead of having to put in the actual table array. In
other words an external way of changing the table array rather than going
into the formula.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
 
A

Aladin Akyurek

If you have a large set of tables to refer to, invoke INDIRECT()...

=VLOOKUP(LookupValue,INDIRECT(X2),ColIdx,MatchType)

Note that X2 shouldn't house a table name which is defined by means of a
dynamic formula with OFFSET() or INDEX().

If the set is small...

=VLOOKUP(LookupValue,CHOOSE(MATCH(X2,{"LookupTableA","LookupTableB","LookupTableC"},0),LookupTableA,LookupTableB,LookupTableC),ColIdx,MatchType)

X2, again, houses a table name.
 
Top