Return a summary of multiple vlookup results

Table1 has a list of incidents. Table2 has a list of actions and whether they are 'In progress' or 'complete'. Some incidents have no actions and some incidents have 3 actions, the status of each action is in table2.

I want table1 to show if all the actions are completed for each incident. Vlookup only returns the status of the first action it finds. I'd like it to find the 3 actions and report if they are all complete. I'm happy to have helper columns. I want it to be live rather than have to click a button.

I've spent a couple of hours on this and can't crack it. any ideas? file attached

Thanks
John

If you change table 1 to

Excel 2016 (Windows) 32 bit

	A	B	C	D
3	Table 1 - Incidents
4		Incident number	Complete	In progress
5		1	1	1
6		2	2	1
7		3	0	0
8		4	0	0
9		5	0	0

Sheet: Sheet1

Formula in C5 copied down & across
=COUNTIFS($B$13:$B$17,$B5,$D$13:$D$17,C$4)

Thank you, this works nicely. Just kept the 'In progress' column and used conditional formatting for colours.

Is there a way for the formula to write 'No Actions' if it can't find a row with an incident number in Table2? when using vlookup i just wrapped with a 'IF' function but that won't work here.

Thanks
John

How about
=IF(COUNTIFS($B$13:$B$17,$B5,$D$13:$D$17,C$4)=0,"No Action",COUNTIFS($B$13:$B$17,$B5,$D$13:$D$17,C$4))

Thank you! brilliant support from Fluff13 and this forum!

You're welcome & thanks for the feedback