Locate the source cell of a LOOKUP function using VBA

zephan.trent

New Member
Joined
Dec 17, 2008
Messages
3
I want to locate the cell that a lookup function (VLOOKUP, HLOOKUP, or INDEX) gets its value from.


For example:

if Cell A2 = "lookup_ref"

then VLOOKUP("lookup_ref",A1:D4,3,False) should return the value of cell C2.


I need to be able to find out what the source cell (i.e. C2 in this case is) is for the lookup functions. I want to do this in VBA.

Using excels find precedents doesn't work for this as it returns the ranges rather than the actual cell address that is looked up.

Does anyone know how I can do this?

Thanks very much.

Zeph
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

In a cell formula it would be
="C" & MATCH("lookup_ref",A1:A4,0)

Now, the important question is...

Why do you want the address of the resulting Cell?
There may (probably will) be an easier way to do what you're trying to do..
 

zephan.trent

New Member
Joined
Dec 17, 2008
Messages
3
Hi jonmo,

Thanks for your quick reply.

What i'm trying to do is find the precedent cell of a lookup function. I.e. I have a model and I want to know where in a large table of data I am looking up a value from. I'm trying to write a macro that will produce a message box which says:

msgbox "this function looks up cell: " & lookup_source

where lookup_source is the cell that the lookup function got it's data from.

Does that make it clearer?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
OK, so you're building a sort of "Excel Tutorial" ?
That's cool..

As far as the resulting cell of a vlookup(or any lookup formula), there's no built in function to get the resulting cell's address. You have to be clever..

You can take the leftmost column # of the range, and add the Colref(-1), that equals column # the resulting cell is in.

You can than use
Application.Match(lookupvalue,Columns(column#),0)
to get the row it was found.

Then use
Cells(Row#,Column#).Address to return the Address.


Does that help?
 

zephan.trent

New Member
Joined
Dec 17, 2008
Messages
3
Thanks, I think that could work.

The problem now is, how do I extract the column reference from the formula. Say I have:

=VLOOKUP(lookup_ref, range, col_num, 0)

Is there a way in VBA to identify the range and the first column of the range, without having to resort to long and messy text functions?

Thanks
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
If you're working with just basic formulas, like for example..

=VLOOKUP(B1,C:R,5,FALSE)
And say that formula is in A1

This will work. But it is specific to a vlookup formula. It should give you ideas though and will require tweaking for different types of formulas.

Rich (BB code):
Sub test()
Dim myarray As Variant, LookupCol As Long, ColRef As Long, LookupValue As String
Dim ResultCol As Long, ResultRow As Variant
 
'Split the formula into parts using a comma as deliminator
myarray = Split(Range("A1").Formula, ",")
 
'finds the left column of range
LookupCol = Range(myarray(1)).Column
 
'finds the colref
ColRef = myarray(2)
 
'finds the lookup value
LookupValue = Right(myarray(0), Len(myarray(0)) - InStr(1, myarray(0), "("))
 
'calculates the resulting column #
ResultCol = ColRef + LookupCol - 1
 
'calculates the resulting row #
ResultRow = Application.Match(Range(LookupValue), Columns(LookupCol), 0)
 
'Test if the value was found
If IsError(ResultRow) Then
    MsgBox "Value Not Found"
Else
    'combines them into an address
    MsgBox Cells(ResultRow, ResultCol).Address
End If
End Sub
 

Forum statistics

Threads
1,082,478
Messages
5,365,783
Members
400,850
Latest member
Raj_Jpr

Some videos you may like

This Week's Hot Topics

Top