Thanks:  0
Likes:  0

# Thread: Formula to identify next cell in column with different value

1. Must leave on travel, will probably be a few days before I can try your formula and respond. Thanks, appreciate all the efforts.

2. Aladin, here's a small sample

{"YEARS","SICK","VACATION";
1,116.25,77.5;2,155,77.5;
3,155,77.5;4,232.5,77.5;
5,232.5,116.25;6,232.5,116.25;
7,310,116.25;8,310,116.25;
9,310,116.25;10,465,116.25;
11,465,116.25;12,465,116.25;
13,465,116.25;14,465,116.25;
15,620,155;16,620,155}

[ This Message was edited by: Juan Pablo G. on 2002-03-19 19:37 ]

3. Meant to include a bit more explanation. Will write formula to determine which yearly anniversary falls in this year. Assume 4th. Sample data shows 232.5 sick and 77.5 vacation earned in current year. Those values get plugged in cells on employee's worksheet. Also want to show when next higher level of sick and vacation will happen. Sample data shows year 7 for sick and year 5 for vacation. Employee worksheet will then show HireDate + 7 years is next sick increment and hours goes to 310; HireDate + 5 years is next vacation increment and hours goes to 116.25.

[ This Message was edited by: pilot on 2002-03-19 19:17 ]

4. On 2002-03-19 19:15, pilot wrote:
Meant to include a bit more explanation. Will write formula to determine which yearly anniversary falls in this year. Assume 4th. Sample data shows 232.5 sick and 77.5 vacation earned in current year. Those values get plugged in cells on employee's worksheet. Also want to show when next higher level of sick and vacation will happen. Sample data shows year 7 for sick and year 5 for vacation. Employee worksheet will then show HireDate + 7 years is next sick increment and hours goes to 310; HireDate + 5 years is next vacation increment and hours goes to 116.25.

[ This Message was edited by: pilot on 2002-03-19 19:17 ]
Pilot,

Thanks providing the sample data & explanation.

I'll assume that A1:C17 in sheet x houses the data you provided, including labels. I'll further assume that you have a sheet called Employee, which has 4 as value (your example) in A2 (either computed or inputted).

In B2 enter:

=OFFSET(x!\$B\$2,MATCH(A2,x!\$A\$2:\$A\$17),0)

In C2 enter:

=OFFSET(x!\$C\$2,MATCH(A2,x!\$A\$2:\$A\$17,0)-1,0)

In A3 enter:

=INDEX(x!\$A\$2:\$A\$17,MATCH(B3,x!\$B\$2:\$B\$17,0))

In B3 enter:

=OFFSET(x!\$B\$2,MATCH(B2,x!\$B\$2:\$B\$17),0)

In C3 enter:

=OFFSET(x!\$C\$2,MATCH(B2,x!\$B\$2:\$B\$17),0)

[ If the formula does not work the way you want it, try

=OFFSET(x!\$C\$2,MATCH(A3,x!\$A\$2:\$A\$17),0)

instead. Both formulas return the same value, but pick out that value differently. ]

A3:C3 should be downwards draggable/copiable.

Alternatively, in x, name the range A2:A17 Years via the Name box, the range B2:B17 SickLevel, and the range C2:C17 Vacation. Then,

in B2 enter:

=INDEX(SickLevel,MATCH(A2,Years))

in C2 enter:

=INDEX(Vacation,MATCH(A2,Years),0)

in A3 enter:

=INDEX(Years,MATCH(B3,SickLevel,0))

in B3 enter:

=INDEX(SickLevel,MATCH(B2,SickLevel)+1)

in C3 enter:

=INDEX(Vacation,MATCH(B2,SickLevel),0)

[ If the formula does not work the way you want it, try

=INDEX(Vacation,MATCH(A3,Years),0)

instead. Both formulas return the same value, but pick out that value differently. ]

A3:C3, like as above, should be downwards draggable/copiable.

That is what I get in the processing area in Employee:

{4,232.5,77.5;
7,310,116.25}

Hope this is what you're looking for.

[ This Message was edited by: Aladin Akyurek on 2002-03-20 14:56 ]

## User Tag List

#### Posting Permissions

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