Formula to identify next cell in column with different value

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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.
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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?

Aladin
 
Upvote 0
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?

Aladin

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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top