If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|