# vlookup cells

#### Michaelpfreem

##### Board Regular
Hello,

Is there any way to have a cell tell you what cell a vlookup formula is pointing to. So for example i have a vlookup formula that looks at an array A1:E10. This formular returns a vaule from a cell (say D3) what i want is a way of seeing what cell that lookup is looking at, not the value in it.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Jonmo1

##### MrExcel MVP
Well, you need to know what value was searched for, and the colref #..

Hope that helps...

#### Michaelpfreem

##### Board Regular
Hello,

Thanks that was great gave me the answer i was looking for unfortunately it hasn't fixed my problem.

What i was hopeing to do was use the answer from the address formula you just provided to put into an offset formula and effectivley have a dynamic offset formula.

For example. i was using the vlookup to find a number in some data. this forms the reference. then below this reference there is a table of data roughly 30 rows by 13 columns. The point been the user can input a number that the vlookup finds and then produces the table of data from below it, this would have been accomplished by using the dynamic offset i was hopeing to produce.

But using your formula i can indeed find the address of the cell that the vlookup is pointing to but unfortunately when i try to use that cell in the offset formula it just references the cell i am pointing to, so for example:

Vlookup formula looks at cell B5, the formula you provided then shows me this cell (B5) this is input to cell J8, i then put the offest formula in

=offset(J8,0,1)

Unfortunately this starts the offset from J8 i was hoping it would use the value of cell J8 but it doesn't, i have tried using your formular as the reference for the offset

Excel doesn't work as it doesn't understand the formula. I have tried several other approachs to getting the value of cell J8 into the offset formula but none seem to work.

Any ideas?

Thanks

#### DonkeyOte

##### MrExcel MVP
=offset(INDIRECT(J8),0,1) should do the trick though you may want to make your row/column offsets more dynamic than that if you're going to use the offset to generate a 30x13 matrix.

Replies
2
Views
353
Replies
1
Views
146
Replies
2
Views
399
Replies
5
Views
219
Replies
6
Views
476

1,190,877
Messages
5,983,361
Members
439,840
Latest member
billy1989

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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