Spreadsheet Practice

How to look up a value with a regular expression in Google Sheets

Google Sheets formula

=FILTER(A2:B9,REGEXMATCH(A2:A9,E1))

Use REGEXMATCH as the condition in FILTER: =FILTER(A2:B9,REGEXMATCH(A2:A9,E1)) returns every row whose text matches the pattern.

Result on the example
a 5-row list that spills from E2 in E2
Excel
See below

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

How it works

REGEXMATCH returns TRUE for each name that matches the pattern. In (?i)desk|table, (?i) makes it ignore case and | means "or", so it finds names containing desk or table. FILTER keeps those rows and spills them.

=FILTER(A2:B9,REGEXMATCH(A2:A9,E1))

Result{5 rows × 2 cols} "Standing desk 48 in", 449, "Desk lamp LED", 39.99, …

  1. A2:B98 rows × 2 cols: "Standing desk 48 in", $449.00, "Desk lamp LED", $39.99, …

    A range: 8 rows × 2 columns.

  2. A2:A98 rows × 1 col: "Standing desk 48 in", "Desk lamp LED", "Office chair mesh", "Side table oak", …

    A range: 8 cells in one column.

  3. E1"(?i)desk|table"

    The value in E1.

  4. REGEXMATCH(A2:A9,E1){8 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …

    REGEXMATCH: TRUE if the text contains a match for the pattern.

    • textA2:A98 rows × 1 col: "Standing desk 48 in", "Desk lamp LED", "Office chair mesh", "Side table oak", …
    • regular_expressionE1"(?i)desk|table"
  5. FILTER(A2:B9,REGEXMATCH(A2:A9,E1)){5 rows × 2 cols} "Standing desk 48 in", 449, "Desk lamp LED", 39.99, …

    FILTER: Rows of a range that meet every condition. #N/A when none match.

    • rangeA2:B98 rows × 2 cols: "Standing desk 48 in", $449.00, "Desk lamp LED", $39.99, …
    • condition1REGEXMATCH(A2:A9,E1){8 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …

In Excel

In Microsoft 365, REGEXTEST works the same way: =FILTER(A2:B9,REGEXTEST(A2:A9,E1)). Older Excel has no regular expressions; use ISNUMBER(SEARCH(…)) for plain "contains" matches.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=FILTER(A2:B9,ISNUMBER(SEARCH("desk",A2:A9))+ISNUMBER(SEARCH("table",A2:A9)))spills 5 × 2The same result without regular expressions, one SEARCH per word. Works in Excel 2021 and later too.

Common mistakes

  • REGEXMATCH is case-sensitive unless the pattern starts with (?i).
  • Characters like . + ( ) and ? have special meanings. Put a backslash before them to match them literally: \..
  • A pattern matches anywhere in the text. Anchor it with ^ (start) or $ (end) when you mean the whole value.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Look up a value with a regular expression

In E2, return the product and price of every product whose name matches the regular expression in E1.

Questions

How do I return only the first match?

Wrap it in INDEX: =INDEX(FILTER(B2:B9,REGEXMATCH(A2:A9,E1)),1).

How do I match codes like AB-1234?

Use a pattern such as ^[A-Z]{2}-\d{4}$.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.