Excel – INDEX / MATCH with different tables and multiple criterias

=IFERROR(EOMONTH(AGGREGATE(14,6,1/(INDEX($H$5:$Z$16,MATCH($G22,$G$5:$G$16,0),)<>"")*($H$4:$Z$4),1),0),"")

Older versions of Excel need this entered with ctrl+shift+enter.

This searches your data for the row where the ID meets the criteria (in this example I used G22 for the ID to match). Aggregate checks for a non-empty cell in that range and returns the value in the header. EOMONTH then returns the last day of that month. If there’s no values or matching ID’s it returns as blank.