# Filter Formula to Infill Matrix based on references from another Table



## MrCameronExcel (Jan 6, 2023)

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).


ABCDEGHIJKLMN2Weekday (1) Weekend (2)1​1​1​1​1​2​3​1​3BlockLevelFlatWindowPriority09/01/2023​10/01/2023​11/01/2023​12/01/2023​13/01/2023​14/01/2023​15/01/2023​16/01/2023​416​119​Large Lounge1​Stage 2516​119​Small Lounge1​

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


ABCDEFG1BlockLevelFlatWindowPriorityStageStart Date216​119​Large Lounge1​Stage 216/01/2023​316​119​Small Lounge1​Stage 216/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 (Jan 6, 2023)

Just a quick Q, Does N4 not spill down?


----------



## MrCameronExcel (Jan 6, 2023)

Skybluekid said:


> Just a quick Q, Does N4 not spill down?


The formula spills downward, yes.


----------



## Skybluekid (Jan 6, 2023)

Not sure why Filter is not doing its job, but here is an alternative:
Book1ABCDEFGHIJKLM2BlockLevelFlatWindowPriorityStageStart Date316119Large Lounge1Stage 216/01/2023416119Small Lounge1Stage 216/01/2023567BlockLevelFlatWindowPriority09/01/202310/01/202311/01/202312/01/202313/01/202314/01/202315/01/202316/01/2023816119Large Lounge1Stage 2916119Small Lounge1Stage 2Sheet1Cell FormulasRangeFormulaM8:M9M8=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 (Jan 6, 2023)

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 (Jan 6, 2023)

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


----------



## MrCameronExcel (Jan 6, 2023)

Skybluekid said:


> 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 (Jan 6, 2023)

Skybluekid said:


> 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 (Jan 6, 2023)

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 (Jan 6, 2023)

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


----------



## MrCameronExcel (Jan 6, 2023)

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).


ABCDEGHIJKLMN2Weekday (1) Weekend (2)1​1​1​1​1​2​3​1​3BlockLevelFlatWindowPriority09/01/2023​10/01/2023​11/01/2023​12/01/2023​13/01/2023​14/01/2023​15/01/2023​16/01/2023​416​119​Large Lounge1​Stage 2516​119​Small Lounge1​

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


ABCDEFG1BlockLevelFlatWindowPriorityStageStart Date216​119​Large Lounge1​Stage 216/01/2023​316​119​Small Lounge1​Stage 216/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 (Jan 6, 2023)

@Fluff Keep forgeting about the new Calc Engine!


----------



## Skybluekid (Jan 6, 2023)

Also got the INDEX/MATCH Option to work
Book1BCDEFGHIJKLMN3BlockLevelFlatWindowPriority09/01/202310/01/202311/01/202312/01/202313/01/202314/01/202315/01/202316/01/2023416119Large Lounge1Stage 2516119Small Lounge1Stage 2Sheet1Cell FormulasRangeFormulaN4:N5N4=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 (Jan 6, 2023)

@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​BlockLevelFlatWindowStage 2Priority09/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 Lounge16/01/2023​1​Stage 216​119​Small Lounge16/01/2023​1​Stage 2


----------



## MrCameronExcel (Jan 6, 2023)

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


----------



## Skybluekid (Jan 6, 2023)

Glad to help


----------

