offset off a vlookup?
 

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.

offset off a vlookup?
 

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)


"txm49" wrote in message
...
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.

offset off a vlookup?
 

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)


"txm49" wrote in message
...
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.

offset off a vlookup?
 

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?


"txm49" wrote:

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.

offset off a vlookup?
 

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)

txm49 wrote:
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.

offset off a vlookup?
 

Thanks to all. These replies were really useful!

"JW" wrote:

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)

txm49 wrote:
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.