Index Match to Find the Next Inspection Due Date

[Yodelayheewho]

This is my first post, so I apologize ahead if I've not followed the forum protocol...

Several retail stores are being inspected on a regular basis. I have the following columns of data in my first table:
Store Number, Due, Actual, SF

I need to send reminders on a weekly basis to inspectors. I need to create a second table that shows the following:
Store Number, Due date for the next inspection.

I need to match the store number between both tables, then return the date in Column B that is the next closest date that is = or > Today.

Please see attached file.

 

[John Jairo V]

Hi, @Yodelayheewho!

I leave three (of many) options for your request.

1. =INDEX(INSPECTIONSCHEDULE,MATCH(1,INDEX(([@Store]=Table12[Store])/(Table12[Actual]=""),),),2)
2. =AGGREGATE(15,6,Table12[Due]/(Table12[Store]=[@Store])/(Table12[Actual]=""),1) <-- Working in Excel 2010 and newer
3. =MINIFS(Table12[Due],Table12[Store],[@Store],Table12[Actual],"") <-- Working in Excel 2019 or Excel for Office 365 suscription

Check file. Blessings!

 

[Yodelayheewho]

I'm not very familiar with AGGREGATE. Looks like I need to become more familiar with it, because this worked beautifully. I have Excel 2013. Thank you so much!!!