Excel Table Filter by Text in Another Cell?

Formulas & functions · Updated November 19, 2026

Yes. To filter an Excel table using text in another cell, add a helper column with a formula that checks each row against the cell, then filter the helper column for TRUE. Excel’s regular table filter menu does not directly link its text-filter criteria to a worksheet cell, but a helper column makes the result update when the cell’s text changes.

Why a helper column is needed

An Excel table’s filter dropdown lets you choose listed values or enter criteria through options such as Text Filters > Contains. Those criteria are entered in the filter interface; they are not a formula reference to a cell such as H2. If you change H2 later, a filter criterion typed into the dropdown will not automatically follow it.

A helper column provides that connection. It evaluates each table row against the current value in the criteria cell and returns TRUE for rows that match. Filtering the helper column to show TRUE displays the matching records. Because the helper formula refers to the criteria cell, changing that cell recalculates the match results. The table filter may need to be reapplied or refreshed to show the changed set, depending on the workbook’s calculation and filter state.

This approach is useful when a colleague needs to search a table by typing a word into a visible input cell, or when a report should respond to a selected customer, product, or status. It also keeps the original table intact: the helper column adds a test, rather than moving or deleting records.

Set up a cell-driven table filter

  1. Make sure the data has headers, select a cell in the range, and choose Insert > Table. Confirm that the table has headers.
  2. Give the table a useful name. Select a table cell, open Table Design, and edit the Table Name box. In this example, the table is named Sales.
  3. Put the search text in a cell outside the table, such as H2. For example, enter invoice in H2.
  4. Add a new table column with a heading such as Match. In its first data row, enter the formula below. The layout assumed here is a Sales table with a Description column and the search text in H2; for rows containing ‘Invoice for Northwind’, ‘Payment received’, and ‘Overdue invoice – Northwind’, the formula returns TRUE, FALSE, and TRUE.

=IF($H$2="",TRUE,ISNUMBER(SEARCH($H$2,[@Description])))

Excel tables normally fill a calculated-column formula down the column for you. If that does not happen, copy the formula through the table’s data rows. The dollar signs lock the reference to H2 as the formula is filled down; the structured reference [@Description] means the Description value in the current row.

  1. Open the filter arrow on the Match heading, clear Select All, select TRUE, and choose OK.
  2. Change H2 to another search term. The Match results recalculate; if the visible rows do not change immediately, clear and reselect TRUE in the Match filter.

To remove the restriction, use the Match dropdown and choose Clear Filter from Match, or select all values in that column. The helper column can remain in the table for the next search, or you can hide it if readers do not need to see the TRUE and FALSE results.

What the formula matches

The example uses SEARCH, which looks for the contents of H2 anywhere within each Description. If H2 contains invoice, both ‘Invoice for Northwind’ and ‘Overdue invoice – Northwind’ match. SEARCH is not case-sensitive, so invoice also matches Invoice. It is a substring search, not a whole-cell equality test.

The IF test at the beginning handles an empty criteria cell. With H2 blank, every row returns TRUE, so the helper-column filter shows all rows rather than treating an empty search as a request for blank descriptions. Remove that blank-cell behavior only if you specifically want an empty criterion to find empty text.

SEARCH also recognizes the wildcard characters * and ? in its search text: an asterisk can stand for any sequence of characters, and a question mark can stand for one character. If people may type those symbols but intend them literally, the search needs additional escaping logic. For ordinary words and phrases, the basic formula is simpler and usually sufficient.

Use an exact match instead

Sometimes a substring is too broad. Searching for cat, for example, could match a description containing the word category. To require the entire cell value to equal the entry, use an equality test instead of SEARCH. The assumed layout is still a Sales table with a Description column and the criterion in H2; if H2 contains the full text ‘Invoice for Northwind’, only a row with exactly that Description returns TRUE.

=IF($H$2="",TRUE,[@Description]=$H$2)

Excel’s normal text equality comparison is not case-sensitive. It does require the cell contents to match as a whole, including spaces and punctuation. If the value in the table has a trailing space but the entry in H2 does not, the comparison will not match. For a case-sensitive exact comparison, Excel has the EXACT function, though you would need to incorporate it into the helper test rather than use the equality formula above.

Keep the filter useful as data changes

Table formulas generally extend to new rows added directly below the table, and table filters include new rows as the table expands. Check that the Match formula appears in added records. If it does not, copy the calculated-column formula down or check Excel’s calculated-column settings. A formula referring to a cell outside the table, such as H2, continues to point to that cell as long as its absolute reference is used.

For a shared workbook, label the input cell clearly: for example, put Search description beside H2 and explain that the Match filter should remain set to TRUE. A data-validation dropdown can also make the input more consistent when the search should be selected from a fixed list. However, validation restricts or guides what users enter; it does not replace the helper formula.

If formulas are set to manual calculation, changing H2 may not update the helper results until calculation runs. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and choose Automatic when appropriate. Calculation mode can affect the whole workbook, so consider other workbook requirements before changing it. On Mac, the Calculation Options command is also on the Formulas tab, though ribbon layout can differ slightly.

Common problems and fixes

  • No rows match: Confirm that H2 contains the intended text, that the helper formula uses the correct table column, and that TRUE is selected in the Match filter. A stray space in the criterion or source text can affect a search.
  • Every row appears: Check whether H2 is blank. The example deliberately returns TRUE for every row when the criterion is blank. Also confirm that the filter is set to TRUE rather than cleared.
  • The wrong records match: SEARCH finds text anywhere in the cell. Use the exact-match version when the complete cell value must be equal, or make the search term more specific.
  • The filter does not respond after editing H2: Verify that the helper results have recalculated, then reapply the TRUE filter. If calculation is manual, recalculate the workbook. Also make sure the formula refers to the intended input cell.
  • The formula shows an error: Check the spelling of the table column header in [@Description]. Structured references depend on the actual header. If the source column contains error values, SEARCH can return an error for those rows; clean or handle those source errors if necessary.

For a regular range that is not an Excel table, the same idea works with ordinary cell references, but the formula must be filled down manually as new records are added. Tables are convenient because they maintain headers, filters, and calculated columns as the data grows.

Alternative: return matching rows with FILTER

If the goal is to display a separate, automatically updating results list rather than hide rows in the original table, the FILTER function can be a better fit. This does not apply a filter to the source table; it spills matching rows into another area. FILTER is available in Microsoft 365 and Excel 2021 or later. Leave enough empty space below and to the right of the formula cell for the results to spill.

The layout assumed here is a Sales table with Item and Description columns, a search term in H2, and three records: item 1001 with ‘Invoice for Northwind’, item 1002 with ‘Payment received’, and item 1003 with ‘Overdue invoice – Northwind’. Entering invoice in H2 makes the formula return rows 1001 and 1003, with all columns from Sales; if H2 is blank, it returns all rows.

=IF($H$2="",Sales,FILTER(Sales,ISNUMBER(SEARCH($H$2,Sales[Description])),"No matches"))

Use the helper-column method when users need to work with the original table’s own filter, sorting, or editing. Use FILTER when a separate report area is more useful. A spilled result is an output array, not an independently editable copy of the table, and it cannot spill into cells that already contain data.

Frequently Asked Questions

Can I put a cell reference directly in Excel’s Text Filters dialog?

Not as a live worksheet reference in the standard table filter dialog. You can type a search value there, but changing another cell will not update that criterion. Use a helper column for a cell-driven table filter, or use FILTER to create a separate results list.

Will the helper-column search ignore uppercase and lowercase differences?

Yes. SEARCH is not case-sensitive, so a criterion such as invoice matches Invoice or INVOICE. For a case-sensitive substring test, use FIND in the helper formula instead; FIND returns an error when the text is not found, so it needs to be handled as a TRUE/FALSE test.

Can I filter for a whole cell value rather than text contained in it?

Yes. Replace the SEARCH test with the equality test shown above. That requires the complete cell value to equal the criterion, rather than merely contain it.

Does this work in Excel for Mac or Excel for the web?

The helper-column method uses standard table formulas and filters, so it works in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon placement and some controls may look different, but the formula logic is the same.

Can the table filter update without a helper column?

Not through the standard filter dropdown using a live reference to another cell. A dynamic FILTER formula can produce a separate matching list, but if you want the original table’s filter to show only matching rows, the helper-column approach is the straightforward option.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.