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

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

**Google Sheets:** `=FILTER(A2:B9,REGEXMATCH(A2:A9,E1))`

**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.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Product | Price |  | Pattern | (?i)desk\|table |
| Standing desk 48 in | 449 |  | Matches |  |
| Desk lamp LED | 39.99 |  |  |  |
| Office chair mesh | 249 |  |  |  |
| Side table oak | 129 |  |  |  |
| Monitor arm dual | 89.5 |  |  |  |
| Writing desk walnut | 359 |  |  |  |
| Bookshelf 5-tier | 149 |  |  |  |
| Coffee table glass | 219 |  |  |  |

Formula in E2: `=FILTER(A2:B9,REGEXMATCH(A2:A9,E1))`

Result (spills 5 × 2): Standing desk 48 in, $449.00; Desk lamp LED, $39.99; Side table oak, $129.00; Writing desk walnut, $359.00; Coffee table glass, $219.00

## 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.

## Other ways

- `=FILTER(A2:B9,ISNUMBER(SEARCH("desk",A2:A9))+ISNUMBER(SEARCH("table",A2:A9)))`: The 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.

## 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}$`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/regex-lookup-google-sheets