vlookup cells

[Michaelpfreem]

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.

Thanks in advance

 

[Jonmo1]

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

=ADDRESS(MATCH(LookupVlaue,A1:A10,0),ColRef)

Hope that helps...

 

[Michaelpfreem]

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

=offset(address(match(B5,A1:A100,0),1),0,1)

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]

=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.