[Solved] Filter questions

[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

 

[shamimarita]

Hello Angleright,

The issue you're facing likely stems from how Excel is interpreting the values in your reference column "L" and the lookup cell R18. Even when formatted as text, Excel sometimes treats purely numeric entries differently from alphanumeric ones in certain functions.

Here’s a way to ensure consistency:

Force Text Comparison: Wrap R18 and L2:L109 with the TEXT function to enforce a text comparison, ensuring both values are treated as text.

Try updating your formula like this:
=IF(R18="","",FILTER(FILTER($H$2:$N$109,(TEXT($L$2:$L$109,"@")=TEXT($R$18,"@")),"NOTHING FOUND"),{0,0,0,0,0,0,1}))

Alternative Method with TO_TEXT: If the TEXT function doesn’t resolve the issue, try using TO_TEXT:

=IF(R18="","",FILTER(FILTER($H$2:$N$109,(TO_TEXT($L$2:$L$109)=TO_TEXT($R$18)),"NOTHING FOUND"),{0,0,0,0,0,0,1}))

This should help Excel treat both numbers and text consistently, allowing the filter to work regardless of whether Plan No contains only numbers or alphanumeric characters.