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]

Just a quick Q, Does N4 not spill down?

 

[MrCameronExcel]

Just a quick Q, Does N4 not spill down?

The formula spills downward, yes.

 

[Skybluekid]

Not sure why Filter is not doing its job, but here is an alternative:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Block	Level	Flat	Window	Priority	Stage	Start Date
3		16	119	Large Lounge	1	Stage 2	16/01/2023
4		16	119	Small Lounge	1	Stage 2	16/01/2023
5
6
7	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
8		16	119	Large Lounge	1								Stage 2
9		16	119	Small Lounge	1								Stage 2
Sheet1

Cell Formulas
Range	Formula
M8:M9	M8	=INDEX($F$3:$F$4,MATCH($B$8&$C$8&$D$8&M$7,$B$3:$B$4&$C$3:$C$4&$D$3:$D$4&$G$3:$G$4,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MrCameronExcel]

I've used that formula but for some reason I am getting #N/A

=INDEX(Programme!$F$2:$F$875, MATCH('Line of Balance'!$B5&'Line of Balance'!$C5&'Line of Balance'!$D5&'Line of Balance'!N$3, Programme!$B$2:$B$875&Programme!$C$2:$C$875&Programme!$D$2:$D$875&Programme!$G$2:$G$875, 0))

 

[Skybluekid]

Have you pressed Control Shift Enter? You will need to do this as it is an Array Formula ie in the MATCH Function

 

[MrCameronExcel]

Have you pressed Control Shift Enter? You will need to do this as it is an Array Formula ie in the MATCH Function

Yes I have tried that, same result #N/A :/

 

[Fluff]

Have you pressed Control Shift Enter? You will need to do this as it is an Array Formula ie in the MATCH Function

There is no need to use CSE with 365.

 

[Skybluekid]

I see the issue. It does not like to reference to another sheet. I have had a look as to why the Filter Function is not working. It seems that when it looks at the Window Column it does not like it. I have put all the other parameters and it works ok.

 

[Fluff]

@MrCameronExcel Check for any leading/trailing spaces on both sheets.
Also check that all the numbers are real numbers & not text.