VLOOKUP cell Address

[Ron Luzius]

I need to do an OFFSET(???, 3,2)

The ??? would be the cell that a VLOOKUP found the Lookup_Value in the
Table_Array.


or

The Vlookup found what it was looking for, but what cell was in in?

Any ideas how I can do this?

 

[Dave Peterson]

You could use application.match()

Dim res as variant 'could be an error
dim somevalue as string 'or what???
dim somerange as range

set somerange = worksheets("somesheet").range("a:a")

res = application.match(somevalue, somerange,0)
if iserror(res) then
msgbox "no match"
else
msgbox somerange(res).offset(0,1).value
end if

 

[Ron Luzius]

I created the function, but how do I get the VLOOKUP result into the
function?

 

[Dave Peterson]

I thought you wanted a VBA solution--since you posted in .programming.

But I'm guessing that you really want a worksheet formula. Check your other
post. You have responses there.