Filter formula help needed with Spill issues

[BrettOlbrys1]

I have the following formula:

=IF($M1="",XLOOKUP($E1,'DATA'!$A:$A,'DATA'!$C:$F),FILTER($M1:$R1,{1,1,0,0,1,1})))

I am applying this formula to cell S1.

If M1 is blank, show data from the DATA sheet cells C:F in cells S1, T1, U1, and V1
If M1 is not blank, filter the data in the current rows data from M1 to R1 and only show cells M1, N1, Q1, and R1 in cells S1, T1, U1, and V1

What I want to add to this formula is another IF statement, such as:


=IF($S1="Unknown",XXX,if($M1="",XLOOKUP($E1,'DATA'!$A:$A,'DATA'!$C:$F),FILTER($M1:$R1,{1,1,0,0,1,1}))))

What I want that XXX to do is fill in cells S1, T1, U1, and V1 with "Unknown" but I don't know how to do that within this rewritten formula?

Any thoughts?

Thanks,

Brett

 

[Fluff]

You should never have a formula that looks at it's own cell, as it creates a circular reference.

 

[BrettOlbrys1]

You should never have a formula that looks at it's own cell, as it creates a circular reference.

Sorry, I mistyped my formula references, here is the correction:

=IF($M1="",XLOOKUP($E1,'DATA'!$A:$A,'DATA'!$C:$F),FILTER($M1:$R1,{1,1,0,0,1,1})))

I am applying this formula to cell T1.

If M1 is blank, show data from the DATA sheet cells C:F in cells T1, U1, V1, and W1
If M1 is not blank, filter the data in the current rows data from M1 to R1 and only show cells M1, N1, Q1, and R1 in cells T1, U1, V1, and W1

What I want to add to this formula is another IF statement, such as:

=IF($S1="Unknown",XXX,if($M1="",XLOOKUP($E1,'DATA'!$A:$A,'DATA'!$C:$F),FILTER($M1:$R1,{1,1,0,0,1,1}))))

What I want that XXX to do is fill in cells T1, U1, V1, and W1 with "Unknown" but I don't know how to do that within this rewritten formula?

 

[Fluff]

Ok, how about

=IF($S1="Unknown",{"xxx","xxx","xxx"},IF($M1="",XLOOKUP($E1,Data!$A:$A,Data!$C:$F),FILTER($M1:$R1,{1,1,0,0,1,1})))

 

[BrettOlbrys1]

Works, great, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.