Calculate the nearest future date to today's date specific to categories in other columns

Sorry if title is confusing...
I'm essentially a beginner with Excel and have been battling my way through Google searches all this time, but I'm truly stuck with this and hoping it's possible. I have tried MINIFS, MIN(IF(AND, INDEX(MATCH and haven't found a way to do it, although I could just be writing the formulas incorrectly.
I have tried to attach an example spreadsheet and I don't think it worked, so have also added an image to the post for reference.

Capture.PNG


So what I'm after is a way to gather the earliest FUTURE date for each of the categories listed in columns A and B. So basically I need a formula that I can tweak for each category/combination that will show me the earliest future dates for each combination of the categories (based on the information I've included in my example). The formula should give me the following dates:
PCA/Lifestyle AM - 25/06/2022
PCA/Lifestyle PM - 1/07/2022
Nursing (EN/RN) AM - 24/06/2022
Nursing (EN/RN) PM - 26/06/2022

The dataset I gave is quite small but I hope I explained it well enough - I'm building this sheet up from nothing and have next to no real data at this stage.

Finally, I don't think an array formula would be appropriate as I need the dates updating automatically and as far as I can tell you need to manually calculate array formulas, so the functionality doesn't align with my needs.

I hope there's someone who can do their Excel magic and help me out! Thanks all :)


EDIT: I have used =INDEX($J:$J,MATCH(1,INDEX(($J:$J>TODAY())*(AD103=$G:$G),0,1),0)) which I thought was working, but it's only showing the first date in the column irrespective of any other dates. For info: Column J is the date list, column G is the discipline list (PCA/Nursing), and AD103 is where I've listed the first discipline name as text for the formula to reference. Not sure if this helps but I figured it was pertinent info.

Assume result list generated from cell G2:I2 down

Please Login or Register  to view this content.

G2 :

Please Login or Register  to view this content.

Copy to H2

I2:

Please Login or Register  to view this content.

Copy G2:I2 down as far as needed

Welcome to the forum.

First off, array formulae do not need any form of manual calculation, so I am not really sure what you understand by that term.

Here's an MS365 dynamic array solution:

=LET(u,UNIQUE(FILTER(A1:B10,A1:A10<>"")),f,IFERROR(1/(1/MINIFS(D1:D10,A1:A10,INDEX(u,,1),B1:B10,INDEX(u,,2),D1:D10,">="&TODAY())),""),CHOOSE({1,2,3},u,INDEX(u,,2),f))

Glad to have helped.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.