# 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

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.

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

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]

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

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)))))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1