Filter question

[angleright]

I am trying to filter on a column(column "L",Plan No) with the following formula
=IF(R18="","",FILTER(FILTER($H$2:$N$109,($L$2:$L$109=$R$18),"NOTHING FOUND"),{0,0,0,0,0,0,1}))
the filter returns work great if the cell contains a number and letter but returns nothing if the cell only contains a number. I have the cells formatted as text. I've tried formatting as general and numeric but still get the same results.
the formula is in row w59

 

[angleright]

When I put that in r60 i get a name error and i still dont get anything in r61

BETA POST.xlsm

drive.google.com

 

[Fluff]

I get a #SPILL error because you have a formula in S60 & S61

 

[angleright]

I"m getting a #NAME? error. Shouldn't a spill error be caused by the cells below. even if I take the formulas out I still get the name error

 

[Fluff]

Shouldn't a spill error be caused by the cells below.

No, because you are trying to return 3 columns.

I"m getting a #NAME? error.

Try re-confirming the formula.

 

[angleright]

same thing, even removed the formulas next to it

BETA POST.xlsm

drive.google.com

 

[Fluff]

If I select R60, click into the formula bar & hit Enter it works

 

[angleright]

Still get the same think, can you send me a link to the file you are using, this is confusing, thanks for your help, im so close to having what i want but not quite there. Its election day here so i'm taking the rest of the day to go sulk.

Thanks Fluff

 

[Fluff]

Unfortunately, I have no way to send you the file.

Are you using 365 as per your profile?

 

[angleright]

LTSC 2021

 

[Fluff]

If that is what you always use, you need to change your profile to show 2021.
Try

=LET(f,FILTER(H2:N7000,L2:L7000=IFERROR(--R18,R18),"NOTHING FOUND"),IF(R18="","",INDEX(f,SEQUENCE(ROWS(f)),{1,3,4})))