How do I insert a dynamic reference of a range inside VLOOKUP?

  • Thread starter Thread starter Alexandre
  • Start date Start date
A

Alexandre

I need to insert a VLOOKUP inside another VLOOKUP in the "Table Array"
definition in order to get different information selected from a big database.
Example: instead of "=VLOOKUP($B7;tab1!$A$4:$N$16500;2;FALSE)"
I would like to search the database area within another array:
"=VLOOKUP($B7;VLOOKUP(C1;A2:B40;2;FALSE);2;FALSE)" where the internal
VLOOKUP should bring the range of the selected area to be used as array in
this formula or
"=VLOOKUP($B7;CELL("CONTENTS";A1);2;FALSE)" where the CELL formula should
bring the array information to be used by VLOOKUP formula.
I realized that this formula, such as INDEX, recognizes the range
information as a "volatile" and does not accept it as "Tab Array" definition.
Is there any other formula that I can use do achieve my results?
Thank you in advance,
Alexandre
 
Alexandre,

VLOOKUP do not return an array but search for a value in the first column of
a table array and returns a value in the same row from another column in the
table array.

If this post helps click Yes
 
Try MATCH() INDEX() combinations

=INDEX(<array>,<row using match()>,<column using match()>)

Try the above and if you have probs.. post back with an example...

If this post helps click Yes
 
The internal vlookup() needs to be something that returns an array.
vlookup() only returns a value. For example:

If G1 thru H3 contain:
1 cat
2 dog
3 fish

and A1 contains:
2

then the formula =VLOOKUP(A1,G1:H3,2) display dog


If B1 contains:
="G1:H3"

then:
=VLOOKUP(A1,INDIRECT(B1),2) will also display dog

You just need to get the cell range for the internal table in some cell and
reference that cell with INDIRECT().
 
Back
Top