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;FAL SE)" 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

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

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
---------------
Jacob Skaria


"Alexandre" wrote:

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;FAL SE)" 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

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

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Alexandre" wrote:

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;FAL SE)" 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

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

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

--
Gary''s Student - gsnu200858


"Alexandre" wrote:

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;FAL SE)" 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

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

Hi Student,

It worked perfectly.
Many thanks,

Alexandre

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200858


"Alexandre" wrote:

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;FAL SE)" 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