Determine cell value when referring to another cell formula I have a vlookup for

Question

Determine cell value when referring to another cell formula

I have a vlookup formula in cell A2 where I would like to reference the cell address used in the vlookup, in another formula. The reason I need to acquire the cell address is to pull in the cell value two columns from that address.

Example:

Cell A2 - = vlookup($g2,stats,2,value)

Cell B2 - reference cell a2 and determine which address is in the vlookup, which is $g2 and then pull in the department's name two cells away from $G2, which would be $I2.

Thanks for help !!

Explanation / Answer

Hi..

Hi,

One way would be to user defined function.

ALT+F11 to open vb editor, right click 'ThisWorkBook' and insert module and paste the code below in on the right. Close VB editor and back on the worksheet call with this in b2

=evalu8(A2)

Function evalu8(Rng As Range) As Variant
Dim Strt As Long, nd As Long
Strt = InStr(1, Rng.Formula, "(", vbTextCompare) + 1
nd = InStr(1, Rng.Formula, ",", vbTextCompare) - 1
evalu8 = Range(Mid(Rng.Formula, Strt, (nd - Strt) + 1)).Offset(, 2)
End Function

Hope this help !!

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.