Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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.



  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default 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.




  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default 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.




  #4   Report Post  
Posted to microsoft.public.excel.misc
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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.



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 638
Default 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.




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP and OFFSET PJ Excel Worksheet Functions 2 July 18th 07 03:28 PM
Using Offset with Vlookup BlackyOakes Excel Discussion (Misc queries) 2 February 12th 07 04:47 PM
VLOOKUP with OFFSET Robert Excel Worksheet Functions 0 July 28th 06 10:20 AM
vlookup and offset Rob M. Excel Worksheet Functions 3 December 2nd 05 02:10 AM
offset and vlookup cutsygurl Excel Worksheet Functions 1 November 5th 04 10:47 PM


All times are GMT +1. The time now is 02:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"