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

B	Mon	21/05/18
B	Tue	22/05/18
B	Wed	23/05/18
B	Thu	24/05/18
B	Fri	25/05/18
	Mon	28/05/18
	Tue	29/05/18
	Wed	30/05/18
	Thu	31/05/18
	Fri	01/06/18
A	Mon	04/06/18
A	Tue	05/06/18
A	Wed	06/06/18
A	Thu	07/06/18
A	Fri	08/06/18

<tbody>
</tbody>

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