# Defining a table_array for VLOOKUP? HELP!!

#### zjhomrighaus

##### New Member
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-

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.

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?

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### sanrv1f

##### MrExcel MVP

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

#### sanrv1f

##### MrExcel MVP
a small correction

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

#### zjhomrighaus

##### New Member
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?

Replies
2
Views
119
Replies
3
Views
174
Replies
1
Views
268
Replies
4
Views
192
Replies
0
Views
139

1,190,613
Messages
5,981,939
Members
439,744
Latest member
Lazerbeakk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back