Excel VBA: Find cell that VLOOKUP references.

[mswoods1]

I'm trying to write some VBA code that returns the cell address that a VLOOKUP function references.

(Example: You have the letters A and B in A1 and A2. Then the numbers 1 and 2 in B1 and B2. Then VLOOKUP("B",A1:B2,2,0) would return the number 2. And with my code, I'd like to be able to return the cell address "B2", where the number 2 is located.)

I tried using the Evaluate method, but it seems to just be returning the value that the VLOOKUP finds, rather than the cell address.

Anyone got any ideas?

Thanks.

 

[Jonmo1]

The important question is...
For what purpose do you want the cell address?

Is it just for display purposes?
Or do you plan to use that cell address in another formula or function?

It is not required to get the address to use it in another formula..

 

[mswoods1]

The important question is...
For what purpose do you want the cell address?

Is it just for display purposes?
Or do you plan to use that cell address in another formula or function?

It is not required to get the address to use it in another formula..

Formula Auditing purposes.

 

[Jonmo1]

Well, I don't think you can get the resulting cell's address from a vlookup.
Because vlookup doesn't return a range object, it only returns the value.

I suppose you could try some kind of Find function, based on the column reference and such..
But that won't be reliable, because whatever result the vlookup had, that particluar cell may not be the only occurance of that value in that column..


If you were using an INDEX/MATCH instead of vlookup, then you could do it with a built in function called CELL..

Given your example vlookup
VLOOKUP("B",A1:B2,2,0)

An equivelent Index Match formula is
=INDEX(B1:B2,MATCH("B",A1:A2,0))

Then you could get the resulting cell address with

=CELL("Address",INDEX(B1:B2,MATCH("B",A1:A2,0)))


This won't work off a Vlookup formula, don't even bother trying..

 

[mswoods1]

This won't work off a Vlookup formula, don't even bother trying..

Hah, that's what I was afraid of. I've been using the evaluate method on index's and offset's to get cell addresses, which seems to work. But I was thinking that it may be impossible to get the same thing for a VLOOKUP, because I wasn't sure if it was the same type of function (as you described, it doesn't return a range).

Thanks for your help.

 

[Jonmo1]

Are you trying to work out a function to get the resulting address from "Any" formula, or just specifically Vlookup??

If it's specifically for vlookup only, then something may be worked out..

Using each component of the Vlookup, You could use code to construct the equivelent Index/Match.
Then put the Index/Match into the Cell formula...

 

[mswoods1]

Are you trying to work out a function to get the resulting address from "Any" formula, or just specifically Vlookup??

If it's specifically for vlookup only, then something may be worked out..

Using each component of the Vlookup, You could use code to construct the equivelent Index/Match.
Then put the Index/Match into the Cell formula...

Hmmm... interesting idea. I'll check it out.

Yeah, I'm trying to construct a little auditing macro that will return the cell address of any type of lookup function. Hopefully I could get it to work with any function and it would be just one macro, though I could construct it in a way that uses different code depending on the beginning of the formula.

Right now, the functions I'm trying to get it to work with are: VLOOKUP, LOOKUP, INDEX, MATCH, INDIRECT, and OFFSET. The idea is that if you're auditing someone else's spreadsheet and they have these formulas, you don't have to spend too much time trying to understand their formula. Instead, you could run a macro to get the cell address of the function, go to that cell address, and make sure their formula is referencing what it is supposed to be.

Again, thanks for the idea regarding VLOOKUP and constructing an INDEX/MATCH function around it. I'll mess around with it later and let you know how it goes.