Thanks:  0
Likes:  0

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

1. Given a column with many repetitive values, how would you write a formula to identify the next cell that differs from the one above it? For example, B2:B6 contain the following values: 50, 50, 50, 75, 75. If a VLOOKUP points you to B3, how would you then reference B5 as the next different cell in the column?

2. What do you mean by "points you to B3"?

Please understand that a VLOOKUP returns a value -- not a cell reference. Furthermore, =VLOOKUP(50,\$B\$2:\$B\$6,1), would cease its search after encountering 50 in cell B2.

[ This Message was edited by: Mark W. on 2002-03-19 10:36 ]

3. Intent is to match a value in Col A, then reference the corresponding value in Col B (B3 in example) in one place and reference next different cell below B3 (B5 in example) in another.

4. On 2002-03-19 11:34, pilot wrote:
Intent is to match a value in Col A, then reference the corresponding value in Col B (B3 in example) in one place and reference next different cell below B3 (B5 in example) in another.
But, do you need a reference to B5 or just the value in B5 (75, in this case)?

5. Just need the value 75. Application is sick/vacation earnings schedule. Col A = years (1 - xx), Col B = number of days (10 for several years, then 15 for several years, then 20, etc.) Once I determine how many years the employee has worked, Col B shows earned vacation days this year. Next incremented value in Col B shows how much earned when it next increases and cell A of that row shows how many years he will have worked by then and can be used with hire date to calculate the date that will occur.

6. On 2002-03-19 09:41, pilot wrote:
Given a column with many repetitive values, how would you write a formula to identify the next cell that differs from the one above it? For example, B2:B6 contain the following values: 50, 50, 50, 75, 75. If a VLOOKUP points you to B3, how would you then reference B5 as the next different cell in the column?
Given the sample

{50;50;50;75;75}

in A2:A6,

=OFFSET(A2,MATCH(50,A2:A6)+1,0)

will give you the first value, which is different from 50 (your lookup value), that is, 75.

Is this what you are looking for?

7. On 2002-03-19 13:30, Aladin Akyurek wrote:

Given the sample

{50;50;50;75;75}

in A2:A6,

=OFFSET(A2,MATCH(50,A2:A6)+1,0)

will give you the first value, which is different from 50 (your lookup value), that is, 75.

Is this what you are looking for?

Make that...

=OFFSET(A2,MATCH(50,\$A\$2:\$A\$6),)

...because =OFFSET(A2,MATCH(50,A2:A6)+1,0) will fail with the following data set...

{50;50;50;75;80}

[ This Message was edited by: Mark W. on 2002-03-19 13:41 ]

8. Very close but if I didn't know what the values in Col B were, I wouldn't know 50 is the value associated with the row that would be selected by matching number of years worked with Col A (see my prev post), so the "50" in your formula would have to be identified relatively, like an OFFSET from selected Col A cell.

9. Perhaps this is what you want...

{=OFFSET(\$A\$2,SUM((OFFSET(A2,,,COUNT(\$A\$2:\$A\$6)-1)=OFFSET(A2,1,,COUNT(\$A\$2:\$A\$6)-1))+0)+1,)}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[ This Message was edited by: Mark W. on 2002-03-19 14:03 ]

10. On 2002-03-19 13:39, pilot wrote:
Very close but if I didn't know what the values in Col B were, I wouldn't know 50 is the value associated with the row that would be selected by matching number of years worked with Col A (see my prev post), so the "50" in your formula would have to be identified relatively, like an OFFSET from selected Col A cell.
Care to construct a small sample or extract a small sample of your own data?

If uou inclined to do so, select an empty cell, type =, select the sample including the labels if any, hit F9, copy what you see, and paste it in the follow-up.

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