Defining a table_array for VLOOKUP? HELP!!

[zjhomrighaus]

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?

 

[sanrv1f]

try "Indirect" instead of "Address"

=VLOOKUP(C21,INDIRECT("A",B2,":","C",B3),7,0)

 

[sanrv1f]

a small correction

=VLOOKUP(C21,INDIRECT("A"&B2&":"&"C"&B3),7,0)

 

[zjhomrighaus]

Still doesn't work

Thanks for the quick response, but the INDIRECT function didn't do the trick (I still get a #VALUE! error). If I try INDIRECT in it's own cell, it returns the value in the cell defined in the argument and what I need is the cell reference itself... no the value.

Put another way, if field A2 has the value of xyz, I don't need xyz, I need "A2" in a format that VLOOKUP will accept.

Any other thoughts?