# recognizing data in adjacent columns

1. ## recognizing data in adjacent columns

Hi

I am trying to find a formula that calculates the longest period from data in an adjacent cell. The attached spreadsheet might better explain this  Register To Reply

2. ## Re: recognizing data in adjacent columns

In cell E1 enter this array formula:

=INDEX(B1:B100,MATCH(2,1/(B1:B100<>"")))

...and press CTRL-SHIFT-ENTER to confirm. The number 2 should appear. If you just press ENTER you will break the array and get a #DIV/0 error.

In E2, put this array formula:

=INDEX(A2:A100,MATCH(INDEX(C2:C100,MATCH(2,1/(C2:C100<>""))),C2:C100,0))

...also confirmed with CSE.  Register To Reply

3. ## Re: recognizing data in adjacent columns

The second array does the trick. I only put two columns in to assist because both questions should return the corresponding data in Column A. Thanks a lot   Register To Reply

4. ## Re: recognizing data in adjacent columns

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]  Register To Reply

5. ## Re: recognizing data in adjacent columns  Register To Reply

6. ## Re: recognizing data in adjacent columns

I thought i had this solved but if any of the data in the adjacent column is the same excel gives the first value  Register To Reply

7. ## Re: recognizing data in adjacent columns

Try this alternate array formula:

=INDEX(A:A,SUMPRODUCT(MAX((B3:B12=INDEX(B3:B12,MATCH(2,1/(B3:B12<>""))))*(ROW(B3:B12)))))  Register To Reply