offset off a vlookup?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to search an array for an initial value and then return the value 2
rows below that value. The initial value could be in an array from a1:j600.
I'm trying to nest a VLOOKUP in an OFFSET, but its not working probably
because offset expects a cell address.

Therefore, I think what I need is to drive the cell address and nest that
within the offset. Don't know how to do that.
 
Don't use VLOOKUP. Nest one OFFSET within another. Assuming your data is in
C6:C15 the following formula will return the value two rows below the first
occurrence of "c".

=OFFSET(OFFSET(C6,MATCH("dd",C6:C15,0)-1,0),2,0)

It will return #N/A if the lookup value to MATCH is not found.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
My previous post was incorrect. Use

=OFFSET(OFFSET(C6,MATCH("c",C6:C15,0)-1,0),2,0)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
If the initial match value willl be in column A
=index(A1:J600, Match(value,A:A,0)+2, column_num)
would work

If the match value can be in any column, it gets more complicated
can it?
 
Something like this work?
Assuming the value you want to return is in range D1:D15 and the range
containing the value you want to match is in range A1:A15. In this
case, we are looking for the word "tommy" in in range A1:A15 and will
return the value 2 rows down from the match in D1:D15.
=OFFSET(INDEX(D1:D15,MATCH("tommy",A1:A15,0)),2,0)
 
Back
Top