zjhomrighaus
New Member
- Joined
- Mar 13, 2009
- Messages
- 3
Hello, I am trying to "manually" enter the table_array in a VLOOKUP function. I basically want to be able to define the table_array using functions nested within the VLOOKUP.
For simplicity, I want to do a VLOOKUP over a range of let's say... A1:C3. I can use the following in a cell by itself to produce "A1:C3"
A B
x 1
y 3
=CONCATENATE("A",B2,":","C",B3)
-and-
=ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4)
These both return A1:C3 for me, but when I embed them in the argument of the VLOOKUP, I get a #VALUE error.
I have tried using TEXT() to convert the output into text, I have tried leaving the result A1:C3 in a cell and referencing that cell in the table_array argument of VLOOKUP... nothing seems to work.
=VLOOKUP(C21, ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4), 7)
Note: in the example above, C21 is the value I want to find in the first row and the 7th column contains the data I want to look up. If I do manually enter in the range A1:C3 in place of the ADDRESS functions, the formula works fine... so it has to do with calculating the table_array
I know it seems obvious that I should just type in A1:C3, but I want to be able to calculate the range based on other criteria and don't want to have to manually enter the range for each VLOOKUP.
Can anyone help?
For simplicity, I want to do a VLOOKUP over a range of let's say... A1:C3. I can use the following in a cell by itself to produce "A1:C3"
A B
x 1
y 3
=CONCATENATE("A",B2,":","C",B3)
-and-
=ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4)
These both return A1:C3 for me, but when I embed them in the argument of the VLOOKUP, I get a #VALUE error.
I have tried using TEXT() to convert the output into text, I have tried leaving the result A1:C3 in a cell and referencing that cell in the table_array argument of VLOOKUP... nothing seems to work.
=VLOOKUP(C21, ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4), 7)
Note: in the example above, C21 is the value I want to find in the first row and the 7th column contains the data I want to look up. If I do manually enter in the range A1:C3 in place of the ADDRESS functions, the formula works fine... so it has to do with calculating the table_array
I know it seems obvious that I should just type in A1:C3, but I want to be able to calculate the range based on other criteria and don't want to have to manually enter the range for each VLOOKUP.
Can anyone help?