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

  • Thread starter Thread starter Ron Luzius
  • Start date Start date
R

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?

I was told to..

How do I get the result from a VLOOKUP into the function?

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
 
Try this:

=OFFSET(INDIRECT(ADDRESS(ROW(Table_array)+MATCH(Lookup_value,Table_array,0)-1,COLUMN(Table_array))),3,2)

Dave
 
You may have to change the MATCH function match_type (third argument) which
is 0 (exact match) in the example. See help.
 
Hello Biff,

If 1st occurence of lookup_value is in A8 for example you'd get a #REF!
error. Perhaps

=INDEX(OFFSET(A2:A10,3,2),MATCH(lookup_value,A2:A10,0))

T. Valko said:
Try something like this:

=INDEX(B2:D10,MATCH(lookup_value,A2:A10,0)+3,2)
Click to expand...
 
If 1st occurence of lookup_value is in A8 for
example you'd get a #REF! error.
Click to expand...

Based on the post I'm "guessing" the table looks something like this (with
additional columns):

x.....1
........2
........3
........4
y.....1
........2
........3
........4

So, my formula is referencing the entire range of data including the empty
cells in the first column.

--
Biff
Microsoft Excel MVP


daddylonglegs said:
Hello Biff,

If 1st occurence of lookup_value is in A8 for example you'd get a #REF!
error. Perhaps

=INDEX(OFFSET(A2:A10,3,2),MATCH(lookup_value,A2:A10,0))
Click to expand...
 
Back
Top