Infill Matrix Based on Multiple Criteria on Rows and Columns (INDEX MATCH...)

[MrCameronExcel]

Hi,

I am looking to fill in the matrix on Table 1, referencing criteria in Table 2. I have only included a sample of total data.

I want to INDEX column 4 in Table 2 so that the matrix in Table 1 specifies which stage (1, 2, or 3) is happening and when it is scheduled.

To do this, I want to MATCH the columns with headers Block and Flat in both Tables, as well as MATCH the date row in Table 1 to the columns Start Date, Day 2, and Day 3 in Table 2.

I worked out the following INDEX MATCH formula to get Stage 1 back, but still missing important formulae.

=INDEX('Contract Programme 09.09.22'!D:D, MATCH(1, ('7-Day Cycle'!C4='Contract Programme 09.09.22'!C:C)*('7-Day Cycle'!A4='Contract Programme 09.09.22'!A:A),0))

I have looked into incorporating SMALL formula to switch between the different results (Stage 1, 2, 3).

Not sure if a helper column/row could be of use?

Any help would be appreciated


Table 1:

Block	Level	Flat	31/10/2022​	01/11/2022​	02/11/2022​	03/11/2022​	04/11/2022​	05/11/2022​	06/11/2022​
Bray	12​	91​		​	​	​	​
Bray	12​	90​
Bray	12​	89​
Bray	12​	88​
Bray	12​	87​
Bray	12​	86​
Bray	12​	85​
Bray	11​	84​
Bray	11​	83​
Bray	11​	82​

Table 2:

Block	Level	Flat	Stage	Start Date	Day 2	Day 3	Duration (d)
Bray	12​	91​	Stage 1	31/10/2022​	01/11/2022​		2​
Bray	12​	91​	Stage 2	02/11/2022​	03/11/2022​		2​
Bray	12​	91​	Stage 3	04/11/2022​	07/11/2022​	08/11/2022​	3​
Bray	12​	90​	Stage 1	02/11/2022​	03/11/2022​		2​
Bray	12​	90​	Stage 2	04/11/2022​	07/11/2022​		2​
Bray	12​	90​	Stage 3	08/11/2022​	09/11/2022​	10/11/2022​	3​
Bray	12​	89​	Stage 1	04/11/2022​	07/11/2022​		2​
Bray	12​	89​	Stage 2	08/11/2022​	09/11/2022​		2​
Bray	12​	89​	Stage 3	10/11/2022​	11/11/2022​	14/11/2022​	3​
Bray	12​	88​	Stage 1	08/11/2022​	09/11/2022​		2​
Bray	12​	88​	Stage 2	10/11/2022​	11/11/2022​		2​
Bray	12​	88​	Stage 3	14/11/2022​	15/11/2022​	16/11/2022​	3​
Bray	12​	87​	Stage 1	09/11/2022​	10/11/2022​		2​
Bray	12​	87​	Stage 2	11/11/2022​	14/11/2022​		2​
Bray	12​	87​	Stage 3	15/11/2022​	16/11/2022​	17/11/2022​	3​
Bray	12​	86​	Stage 1	10/11/2022​	11/11/2022​		2​
Bray	12​	86​	Stage 2	14/11/2022​	15/11/2022​		2​
Bray	12​	86​	Stage 3	16/11/2022​	17/11/2022​	18/11/2022​	3​
Bray	12​	85​	Stage 1	11/11/2022​	14/11/2022​		2​
Bray	12​	85​	Stage 2	15/11/2022​	16/11/2022​		2​
Bray	12​	85​	Stage 3	17/11/2022​	18/11/2022​	21/11/2022​	3​
Bray	11​	84​	Stage 1	14/11/2022​	15/11/2022​		2​
Bray	11​	84​	Stage 2	16/11/2022​	17/11/2022​		2​
Bray	11​	84​	Stage 3	18/11/2022​	21/11/2022​	22/11/2022​	3​
Bray	11​	83​	Stage 1	16/11/2022​	17/11/2022​		2​
Bray	11​	83​	Stage 2	18/11/2022​	21/11/2022​		2​
Bray	11​	83​	Stage 3	22/11/2022​	23/11/2022​	24/11/2022​	3​

 

[jasonb75]

Is this what you need? I've set the formulas to the actual size of table 2 in your example, using full columns in a formula like this is not a good idea (unless you want to wait a few days for them to process).

Book1
	A	B	C	D	E	F	G	H	I	J
1	Block	Level	Flat	31/10/2022	01/11/2022	02/11/2022	03/11/2022	04/11/2022	05/11/2022	06/11/2022
2	Bray	12	91	Stage 1	Stage 1	Stage 2	Stage 2	Stage 3
3	Bray	12	90			Stage 1	Stage 1	Stage 2
4	Bray	12	89					Stage 1
5	Bray	12	88
6	Bray	12	87
7	Bray	12	86
8	Bray	12	85
9	Bray	11	84
10	Bray	11	83
11	Bray	11	82
Sheet3

Cell Formulas
Range	Formula
D2:J11	D2	=FILTER('Contract Programme 09.09.22'!$D$2:$D$28,('Contract Programme 09.09.22'!$A$2:$A$28=$A2)*('Contract Programme 09.09.22'!$B$2:$B$28=$B2)*('Contract Programme 09.09.22'!$C$2:$C$28=$C2)*(('Contract Programme 09.09.22'!$E$2:$E$28=D$1)+('Contract Programme 09.09.22'!$F$2:$F$28=D$1)+('Contract Programme 09.09.22'!$G$2:$G$28=D$1)),"")