1. ## 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. ## Re: Offset/Match (2003)

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

3. ## Re: Offset/Match (2003)

Try

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

4. ## Re: Offset/Match (2003)

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

5. ## 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. ## Re: Offset/Match (2003)

What kind of values does column X contain?

7. ## Re: Offset/Match (2003)

Positives and negatives

8. ## 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).

Thanks Hans.

10. ## 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
•