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


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
 
Ad

Advertisements

J

Jacob Skaria

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
 
J

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
 
G

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:
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().
 
Ad

Advertisements


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

Top