Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Offset/Match (2003)

    Trying to return the contents of a cell in column C that is in the same row as the last value (non-zero) in column X. I am trying to use "=OFFSET(INDEX(X:X,MATCH(9.99999999999999E+307 ,B:),0,-10,1,1)", but it returns a zero. Any ideas?
    Thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Offset/Match (2003)

    =INDEX(C:C,MATCH(9.99999999999999E+307,B:B,1))
    should work if the values in B are numeric.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset/Match (2003)

    Try

    =INDEX(C:C,MATCH(9.99999999999999E+307,X:X))

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Offset/Match (2003)

    Oops - I misread the question. I was wondering where column X came into the formula...
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Offset/Match (2003)

    Oops! I forgot to mention I am looking for the date that corresponds to the last positive value in column X.
    Sorry.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset/Match (2003)

    What kind of values does column X contain?

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Offset/Match (2003)

    Positives and negatives

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset/Match (2003)

    Try the following array formula (confirm with Ctrl+Shift+Enter):

    =INDEX(C1:C1000,MAX(ROW(1:1000)*(X1:X1000>0)))

    Replace 1000 with an appropriate upper bound (you can't use this formula on an entire column).

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Offset/Match (2003)

    Thanks Hans.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset/Match (2003)

    A slight variation of the formula (also as an array formula):

    =INDEX(C1:C1000,MAX(IF(X1:X1000>0,ROW(1:1000))))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •