Filter Formula to Infill Matrix based on references from another Table

[MrCameronExcel]

Hi,

I am trying to fill in the below matrix based on references to another table using the FILTER formula, but the current formula is not capturing all the values desired.

Matrix is below (column F is hidden).

A

B

C

D

E

G

H

I

J

K

L

M

N

2

Weekday (1) Weekend (2)

1​

1​

1​

1​

1​

2​

3​

1​

3

Block

Level

Flat

Window

Priority

09/01/2023​

10/01/2023​

11/01/2023​

12/01/2023​

13/01/2023​

14/01/2023​

15/01/2023​

16/01/2023​

4

16​

119​

Large Lounge

1​

Stage 2

5

16​

119​

Small Lounge

1​

N4 Formula: =FILTER(Programme!$F$2:$F$875,(Programme!$B$2:$B$875='Line of Balance'!$B4)*(Programme!$C$2:$C$875='Line of Balance'!$C4)*(Programme!$D$2:$D$875='Line of Balance'!$D4)*(Programme!$G$2:$G$875='Line of Balance'!N$3), "")

N5 Formula: =FILTER(Programme!$F$2:$F$875,(Programme!$B$2:$B$875='Line of Balance'!$B5)*(Programme!$C$2:$C$875='Line of Balance'!$C5)*(Programme!$D$2:$D$875='Line of Balance'!$D5)*(Programme!$G$2:$G$875='Line of Balance'!N$3), "")


Programme Table

	A	B	C	D	E	F	G
1	Block	Level	Flat	Window	Priority	Stage	Start Date
2		16​	119​	Large Lounge	1​	Stage 2	16/01/2023​
3		16​	119​	Small Lounge	1​	Stage 2	16/01/2023​

The cell N5 in the matrix should be "Stage 2" but it is showing blank for some reason.

Thanks for your help.

 

[Skybluekid]

@Fluff Keep forgeting about the new Calc Engine!

 

[Skybluekid]

Also got the INDEX/MATCH Option to work

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Block	Level	Flat	Window	Priority	09/01/2023	10/01/2023	11/01/2023	12/01/2023	13/01/2023	14/01/2023	15/01/2023	16/01/2023
4		16	119	Large Lounge	1								Stage 2
5		16	119	Small Lounge	1								Stage 2
Sheet1

Cell Formulas
Range	Formula
N4:N5	N4	=INDEX(Sheet2!$G$3:$G$4,MATCH(Sheet1!C4&Sheet1!D4&Sheet1!E4&Sheet1!$N$3,Sheet2!$C$3:$C$4&Sheet2!$D$3:$D$4&Sheet2!$E$3:$E$4&Sheet2!$H$3:$H$4,0))

 

[MrCameronExcel]

@Skybluekid I figured out why the original equation wasn't working, because the second date I was referencing was 16/02/23 + 0.5 (A MIDDAY DATE!).

I took your advise thought and merged the two tables which has also allowed me to simplify the equation to a simple =IF(OR(....), ...., ....)

Looks like this now, with the formula in (now) O5 being =IF(OR(O$3=$E5, O$3+0.5=$E5), $E$3, "")

Weekday (1) Weekend (2)

1​

1​

1​

1​

1​

2​

3​

1​

Block

Level

Flat

Window

Stage 2

Priority

09/01/2023​

10/01/2023​

11/01/2023​

12/01/2023​

13/01/2023​

14/01/2023​

15/01/2023​

16/01/2023​

16​

119​

Large Lounge

16/01/2023​

1​

Stage 2

16​

119​

Small Lounge

16/01/2023​

1​

Stage 2

 

[MrCameronExcel]

@Skybluekid Your index match also worked when I accounted for the midday date. Thanks

 

[Skybluekid]

Glad to help