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



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,



Jacob Skaria


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

Jacob Skaria

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

Gary''s Student

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:

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

If B1 contains:

=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().


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question