Function Problems

Use FILTER and SEARCH to Return Selected Excel Table Columns

Ayan MasoodAyan Masood Nov 18, 2026 868 views

Question details

Return only the first three columns of an imported Azure DevOps table for rows whose Tags contain Reviewed.

Use FILTER and SEARCH to Return Selected Excel Table Columns
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user has a working FILTER and SEARCH formula and wants to limit its output to selected table columns.
Observed behavior
The current formula finds matching rows but returns more columns than needed.
Before you start

Check the table name and confirm that ID through Full Name are the three adjacent columns you want to return. Use a version of Excel that supports FILTER, and leave enough empty cells for the results to spill.

Solution 1Recommended

Filter a Structured Reference to the First Three Columns

Use this approach when the columns you want are adjacent and run from ID through Full Name.

1
Select an output cell

Click an empty cell outside the source table where the filtered results can spill into neighboring cells.

2
Enter the formula

In the formula bar, enter `=FILTER(VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1[[ID]:[Full Name]],ISNUMBER(SEARCH("Reviewed",VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1[Tags])))` and press Enter.

3
Check the results

Verify that the output contains the columns from ID through Full Name and only rows with Reviewed anywhere in Tags. Adjust the table or column names in the formula bar if your headers differ.

Filter a Structured Reference to the First Three Columns
How the search works: SEARCH matches partial text without regard to letter case. If no rows match, FILTER returns a calculation error unless you supply an optional value for its if_empty argument.
Work with formulas in WPS Spreadsheets

Filter Selected Columns in WPS Spreadsheets

In a WPS Spreadsheets version that supports FILTER, use a range containing the desired columns and SEARCH the Tags range for Reviewed.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Identify the ranges: Check the column letters and row numbers for the three output columns and the Tags column. For example, the output might be A2:C100 and Tags might be D2:D100.
  3. 3. Enter the formula: Click an empty cell outside the source data and enter `=FILTER(A2:C100,ISNUMBER(SEARCH("Reviewed",D2:D100)),"")`. Replace the example ranges with your actual ranges, using the same start and end rows.
  4. 4. Review the output: Press Enter and check that the results spill into empty cells. If FILTER is not recognized, check function availability in your WPS version.
Return matching rows while limiting the displayed columnsSearch for Reviewed as partial text within TagsOpen and save Microsoft Excel XLSX workbooksWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Does SEARCH match Reviewed inside longer tag text?

Yes. SEARCH finds Reviewed anywhere in the Tags cell and is not case-sensitive, so it also matches text such as Reviewed; Approved.

How can I show a message when no rows match?

Add an if_empty argument to FILTER, such as `=FILTER(A2:C100,ISNUMBER(SEARCH("Reviewed",D2:D100)),"No matches")`.

Why does Excel show a #SPILL! error?

Check the cells below and to the right of the formula and clear any blocking contents. Place the formula outside an Excel table so its results can spill.

Can I return columns that are not adjacent?

Yes. If your Excel version supports CHOOSECOLS, use it as the FILTER array and provide the positions of the columns you want.