Search column for next of a given value?

[dobby1303]

Hi all,

I'm trying to search through a column of A's, B's and null return values for the next instance of A or B, can anyone help?

The formula I'm working with is currently working with dates to find the date of the next monday in a week A or B (school 2 week timetable), when I get to school holidays it is neither week A or B, so the null value is returned. At the moment the formula is set to find date 1 week after. The problem I have is that it's not always 1 week later I'm looking for, sometimes it's two weeks, is there a formula that can do this for me?

At the moment I have:

=IF(OR(INDEX(C:C,MATCH(TODAY()+(7-WEEKDAY(TODAY(),2)+1),E:E))="A",INDEX(C:C,MATCH(TODAY()+(7-WEEKDAY(TODAY(),2)+1),E:E))="B"),TODAY()+(7-WEEKDAY(TODAY(),2)+1),TODAY()+(7-WEEKDAY(TODAY(),2)+8))

Basically, if next monday's date is in A or B, return next monday's date, if not return the monday after.
It's that final part in bold that I'm looking to change to "search for the next monday with A or B".

 

[dobby1303]

sorry, that should say (edit button has disappeared):

Basically, if next monday's date (in column E) is in week A or B (in column C), return next monday's date, if not return the monday after.

 

[shg]

Can you post an example of your data layout and expected results?

 

[dobby1303]

I'm looking for it to return the date of the next Monday which falls in a week A or week B, it is working at the moment, as long as that is either the coming Monday, or the one after, I can't seem to get it to work if I need it to be potentially the Monday after that.

the data looks like this, although the issue comes from longer than a week of null values

[TABLE="class: grid, width: 151"]
<tbody>[TR]
[TD]B[/TD]
[TD]Mon[/TD]
[TD]21/05/18[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Tue[/TD]
[TD]22/05/18[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Wed[/TD]
[TD]23/05/18[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Thu[/TD]
[TD]24/05/18[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Fri[/TD]
[TD]25/05/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mon[/TD]
[TD]28/05/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tue[/TD]
[TD]29/05/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Wed[/TD]
[TD]30/05/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Thu[/TD]
[TD]31/05/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fri[/TD]
[TD]01/06/18[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Mon[/TD]
[TD]04/06/18[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Tue[/TD]
[TD]05/06/18[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Wed[/TD]
[TD]06/06/18[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Thu[/TD]
[TD]07/06/18[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Fri[/TD]
[TD]08/06/18[/TD]
[/TR]
</tbody>[/TABLE]

Should return "04/06/18", if the break is longer than 2 weeks it doesn't work though