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]

If I'm following correctly, this should do what you need.

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

If it doesn't work then it could mean that the dates in column B are not in a valid format, which could be a problem.

edit:- second version of the formula, the one above will return the dates from column B in chronological order (oldest date first, change the beginning of the formula from 15;6 to 14;6 to reverse the order), the one below will return them in the same order as column B (if not chronological).

=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);ROWS($A$4:$A4)));"")

 

[SteveNL86]

Thanks Jason, the second formula seems to do the trick! Awesome!

 

[SteveNL86]

I have one issue though, if B13 is blank its not suppose to show any results, which is why I was using the Iferror formula. Now when the cell is empty it is showing 1900-01-00 in all cells.

 

[jasonb75]

That is a simple fix,

=IF(OR($B$13="";A3="");"";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)));""))

IFERROR doesn't actually catch the fact that B13 is empty, rather it blanks the cells when the row count in ROWS($A$4:$A4) exceeds the number of valid results.
I've edited the formula to ignore empty cells in B2:B5000 when it calculates. If B13 is empty then it will show a blank without even processing the rest of the data.

I've also added an extra slight change that should speed it up a bit if you have the formula filled into more cells than needed to be sure that all results are shown, although that might not work as intended is A3 is empty or part of a merged cell. If that proves to be the case then removing ;A3="" from the first part should rectify that.

 

[SteveNL86]

Well it was working last night and I do think your suggetion is correct.

However, I was messing around with the format of the dates, to keep a hyperlink working, and now I'm not getting any results.

I'm using a custom format of "dd-mm-jjjj" in both B13 as in range B2:B5000.

 

[jasonb75]

Format is only visual, is shouldn't make any difference to the formulas.

The exceptions being text format (which would show dates as a 5 digit serial number) or a formula in B13 that uses the TEXT function.

What if you go back to the original formula, do you see results then?

 

[SteveNL86]

No, the original formula has stopped working aswell. So it must have been something i've done with changing the format.

 

[jasonb75]

Do any of the cells referred to in the formula use the TEXT function (or equivalent if you're translating to a different language)?

You mention changing the format for a hyperlink to work, but changing format would not make any difference to a hyperlink (assuming hyperlink formula, other methods may be affected in different ways) so there could be something else less obvious.

A formula will always see a date as serial number regardless of format so a change to that alone will not do anything.
Text dates are the exception, these are easily identified by trying to change the format. A proper date will always follow the selected format, a text date will not change.

 

[SteveNL86]

No, they are not.

B13 has a date that I put it manually, and B3 has a name that I put in manually aswell or that I select from a dropdown list.

I'm still using this formula:

=IFERROR(INDEX(Rooster2020!$B$2:$B$5000;SMALL(IF(ISNUMBER(MATCH(Rooster2020!$O$2:$O$5000;Registratie!$B$3;0));MATCH(ROW(Rooster2020!$O$2:$O$5000);ROW(Rooster2020!$O$2:$O$5000));"");ROWS($A$4:$A4)));"")

To get the info from older years. And that one is working fine. So the only issue is not getting the dates past the date that i've put in manually.