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.
 

jasonb75

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

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

SteveNL86

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

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

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Ive made an error though. In that post I asked for a solution if B13 is empty, I mend B3.
 

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

jasonb75

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

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

A3 is empty unfortunately
 

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
I have changed it just to

VBA Code:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,420
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad we got there eventually.

Thanks for your feedback :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,247
Messages
5,623,603
Members
415,981
Latest member
Baltwin

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