Forum Discussion

doktorj's avatar
doktorj
Copper Contributor
May 30, 2025
Solved

filter stopped working

I have the following in my spreadsheet: =IFERROR(TEXTJOIN(",", TRUE, FILTER(daily!$AL$5:$AL$1001, (daily!$AJ$5:$AJ$1001=1) * (daily!$X$5:$X$1001=D86) * (daily!$AN$5:$AN$1001=D87))), "") where D86 has...
excel
Formulas and Functions
  • doktorj's avatar
    doktorj
    Jun 01, 2025

    Hi Patrick, many thanks for that suggestion. There were several #N/As in the spill results, and after correcting these, it's all working again. Best regards.

Chris_Apps4Rent's avatar
Chris_Apps4Rent
Brass Contributor
May 31, 2025

The issue is likely with the values in column AN. Even if they look fine, they might have hidden characters, extra spaces, or formatting issues. Here are quick things to try:

  1. Check for hidden spaces: Use
    =TRIM(CLEAN(daily!AN5))=TRIM(CLEAN(D87))
    to see if the values truly match.
  2. Check data type: Use
    =ISTEXT(daily!AN5)
    to confirm all cells are text.
  3. Try using cleaned values in the formula:=IFERROR(TEXTJOIN(",", TRUE, FILTER(daily!$AL$5:$AL$1001, (daily!$AJ$5:$AJ$1001=1) * (daily!$X$5:$X$1001=D86) * (TRIM(CLEAN(daily!$AN$5:$AN$1001))=TRIM(CLEAN(D87))) )), "")

It’s probably a formatting or character mismatch in column AN. Hope this helps!

doktorj's avatar
doktorj
Copper Contributor
Jun 04, 2025

Hi Chris, thanks for the reply. I'd tried those suggestions before posting here, but they didn't help. The earlier reply from Patrick fixed things. 

Resources