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

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
If I'm following correctly, this should do what you need.
Excel Formula:
=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).
Excel Formula:
=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)));"")
 
Last edited:
Solution

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Thanks Jason, the second formula seems to do the trick! Awesome!
 

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That is a simple fix,
Excel Formula:
=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

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
No, the original formula has stopped working aswell. So it must have been something i've done with changing the format.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

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
Top