Inserting IF formula in complex index/match formula to get results until certain date

[SteveNL86]

Dear reader,

I'm using this formula:
=IFERROR(INDEX(Rooster2021!$B$2:$B$5000;SMALL(IF(ISNUMBER(MATCH(Rooster2021!$N$2:$N$5000;Registratie!$B$3;0));MATCH(ROW(Rooster2021!$N$2:$N$5000);ROW(Rooster2021!$N$2:$N$5000));"");ROWS($A$4:$A4)));"")
And its working great, its giving me the matching results that I am after.

However, I'm keeping track of the last date the document was updated in cell B13. I'm trying to add an IF formula to show me matches that occur on or before the date specified in B13.

So if the dates in B2:B5000 in sheets 2021 are smaller or equal to the date in B13 it should give me the matching results, if the date is larger it should ignore the results.

Any help is much appreciated.
Kind regards.

 

[jasonb75]

From what I can see, you would need to select the dates in rooster2021 B2:B5000, then go through text to columns.
Select delimited on the first page of options.
Uncheck all of the boxes on the second page.
Choose DMY (or DMJ if translated) in the third page, then click finish.

Then enter your date into B13 in dd/mm/jjjj format and it should work (I hope).

 

[SteveNL86]

I've tried restoring the dates with the original data and format YYYY/MM/DD. I also changed B13 to this format.

But now I can't even get your original solution to work, eventhough it did work yesterday.

 

[SteveNL86]

Ok, with your text to column awnser I have got the first solution you posted to work again.

However, the formula with the IF/OR solution, in case B3 is empty is not giving any results. Atleast we've made some progress!

 

[SteveNL86]

Ive made an error though. In that post I asked for a solution if B13 is empty, I mend B3.

 

[SteveNL86]

Ive made an error though. In that post I asked for a solution if B13 is empty, I mend B3.

 

[jasonb75]

In that case it should be as simple as changing $B$13 to $B$3.

Although, as I said before the OR part might not work if A3 is merged or empty.

 

[SteveNL86]

A3 is empty unfortunately

 

[SteveNL86]

I have changed it just to

=IF($B$3="";"";IFERROR(INDEX(Rooster2021!$B:$B;AGGREGATE(15;6;ROW(Rooster2021!$B$2:$B$5000)/(Rooster2021!$N$2:$N$5000=Registratie!$B$3)/(Rooster2021!$B$2:$B$5000<=$B$13)/(Rooster2021!$B$2:$B$5000>0);ROWS($A$4:$A4)));""))

And that seems to work great.

Thanks again for all your help Jason!!!

 

[jasonb75]

You're welcome, glad we got there eventually.

Thanks for your feedback