Lookup & Reference
Select columns with a header-driven horizontal FILTER mask
Build a smaller output view using literal header comparisons and a horizontal keep mask; source columns remain in the workbook.
Remember: FILTER works HORIZONTALLY across columns—not just vertically across rows!
FILTER can select columns when its horizontal mask has the same width as the source. This example uses two optional excluded header labels in G2:G3 and five nonempty source headers. Blank exclusion cells are ignored.
The result is a view, not redaction or access control: excluded values remain in the source workbook, history and any shared copy that contains them. Do not share the source workbook on the assumption that FILTER removes confidential data. Review a separate values-only output and its contents before distributing it.
Literal comparisons avoid COUNTIF wildcard interpretation of headers containing * or ?. Duplicate header labels are all handled alike. The ranges are fixed; adding a sixth column requires updating the mask and output ranges.
| Column Header (Row 1) | Listed in Exclude G2:G3? | Keep Mask (Row 2) | Included in output view? |
|---|---|---|---|
| EmpID (Col A) | No | 1 | Kept (Column 1) |
| SSN (Col B) | Yes ("SSN") | 0 | Excluded from this view only |
| Dept (Col C) | No | 1 | Kept (Column 2) |
| Salary (Col D) | Yes ("Salary") | 0 | Excluded from this view only |
| Region (Col E) | No | 1 | Kept (Column 3) |
Header-driven 1/0 Keep-Mask and horizontal FILTER
Join the retained header labels. This text preview does not return the data rows. The next section shows the actual horizontal data spill.
=IF(OR(COUNTBLANK(A1:E1)>0,COUNT(A2:E2)<>5,COUNTIF(A2:E2,0)+COUNTIF(A2:E2,1)<>5),"Invalid inputs",IF(SUM(A2:E2)=0,"No columns",TEXTJOIN(" | ",TRUE,FILTER(A1:E1,A2:E2=1))))Count retained columns after horizontal filtering
Sum a validated binary mask to count retained columns, including zero when none remain.
=IF(OR(COUNTBLANK(A1:E1)>0,COUNT(A2:E2)<>5,COUNTIF(A2:E2,0)+COUNTIF(A2:E2,1)<>5),"Invalid inputs",SUM(A2:E2))Return the selected data columns
Enter this formula in an empty area outside A1:G4 with room for the spilled result. Existing cells must not block its output.
An invalid header or mask deliberately produces a division error in the include expression, rather than returning an apparently valid partial view. Fix the source header or mask before using the output.
=FILTER(A3:E4,(A2:E2=1)/IF(OR(COUNTBLANK(A1:E1)>0,COUNT(A2:E2)<>5,COUNTIF(A2:E2,0)+COUNTIF(A2:E2,1)<>5),0,1),"No columns")Try the example
Use the original values or change them to test your understanding.