=filter(Addresses!A2:A600,--(Addresses!D2:D600 = 1))

Should only filter on the values you actually want.

Filter Data Without Filter Column

• You started your post letting one to believe that you wanted to filter your data excluding the filter column. If that’s the case, you could do the following.

Readable and Maintainable

=LET(data,Addresses!A2:G6,filter_column,4,criterion,1,
    c,SEQUENCE(COLUMNS(data)),
    s,FILTER(c,c<>filter_column),
    f,CHOOSECOLS(data,filter_column)=criterion,
    r,FILTER(CHOOSECOLS(data,s),f),
    r)

Alternatively, you could use s,TOROW(IFS(c<>filter_column,c),2),.

Compact

(After Removing the Redundant s, f and r Variables)

=LET(data,Addresses!A2:G6,filter_column,4,criterion,1,
    c,SEQUENCE(COLUMNS(data)),
    FILTER(CHOOSECOLS(data,FILTER(c,c<>filter_column)),
        CHOOSECOLS(data,filter_column)=criterion))

LAMBDA function

• With a few simple changes (first line and trailing parentheses), you could create a LAMDBA formula…

  Plain text

  Copy to clipboard

  Open code in new window

  EnlighterJS 3 Syntax Highlighter

  <code> =LAMBDA(data,filter_column,criterion,LET(

  c,SEQUENCE(COLUMNS(data)),

  FILTER(CHOOSECOLS(data,FILTER(c,c<>filter_column)),

  CHOOSECOLS(data,filter_column)=criterion)))

  </code>

  <code> =LAMBDA(data,filter_column,criterion,LET( c,SEQUENCE(COLUMNS(data)), FILTER(CHOOSECOLS(data,FILTER(c,c<>filter_column)), CHOOSECOLS(data,filter_column)=criterion))) </code>

   =LAMBDA(data,filter_column,criterion,LET(
       c,SEQUENCE(COLUMNS(data)),
       FILTER(CHOOSECOLS(data,FILTER(c,c<>filter_column)),
           CHOOSECOLS(data,filter_column)=criterion)))
  

  … to use it in the Name Manager to create your own LAMBDA function and e.g. name it FilterNFC.

• Then you could use it as any other Excel function anywhere in the workbook:

  Plain text

  Copy to clipboard

  Open code in new window

  EnlighterJS 3 Syntax Highlighter

  <code> =FilterNFC(Addresses!A2:G6,4,1)

  </code>

  <code> =FilterNFC(Addresses!A2:G6,4,1) </code>

   =FilterNFC(Addresses!A2:G6,4,1)
  

Google Sheets

• Here’s a link to Filter No Filter Column on my Google Drive.
• I got it to work by slipping in an ARRAYFORMULA for the f variable.

=LET(data,Addresses!A2:G6,filter_column,4,criterion,1,
    c,SEQUENCE(COLUMNS(data)),
    s,FILTER(c,c<>filter_column),
    f,ARRAYFORMULA(CHOOSECOLS(data,filter_column)=criterion),
    r,FILTER(CHOOSECOLS(data,s),f),
    r)

• It didn’t let me break the resulting line.

=LET(data,Addresses!A2:G6,filter_column,4,criterion,1,
    c,SEQUENCE(COLUMNS(data)),
    FILTER(CHOOSECOLS(data,FILTER(c,c<>filter_column)),ARRAYFORMULA(CHOOSECOLS(data,filter_column)=criterion)))