# How to make a search box in Excel

Search all the columns joined together: `=FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")` returns every row that contains the text typed in `G1`.

**Excel:** `=FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=IFNA(FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10))),"No results")`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Name | Company | City | Phone |  | Search | lake |
| Ana Ruiz | Northwind | Salt Lake City | 555-0141 |  |  |  |
| Raj Patel | Lakeside Dental | Denver | 555-0172 |  |  |  |
| Lee Chen | Contoso | Boston | 555-0158 |  |  |  |
| Kim Ortiz | Fabrikam | Austin | 555-0133 |  |  |  |
| Mo Diaz | Blake & Sons | Portland | 555-0190 |  |  |  |
| Sam Cole | Litware | Lake Forest | 555-0117 |  |  |  |
| Eve Lund | Adatum | Miami | 555-0126 |  |  |  |
| Noor Ali | Tailspin | Chicago | 555-0184 |  |  |  |
| Ivy Shah | Wingtip | Seattle | 555-0165 |  |  |  |

Formula in F3: `=FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")`

Result (spills 4 × 4): Ana Ruiz, Northwind, Salt Lake City, 555-0141; Raj Patel, Lakeside Dental, Denver, 555-0172; Mo Diaz, Blake & Sons, Portland, 555-0190; Sam Cole, Litware, Lake Forest, 555-0117

## How it works

Joining the name, company and city with spaces gives one line of text per contact. SEARCH looks for the search text anywhere in that line, ignoring case, and ISNUMBER turns its result into TRUE or FALSE for FILTER. Typing in `G1` and pressing Enter redraws the list, which is all a search box needs.

Note that "lake" also finds Blake & Sons: SEARCH matches inside words.

## Other ways

- `=FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10)),"No results")`: Search one column only, here the company. (Only Lakeside Dental and Blake & Sons.)
- `=FILTER(A2:D10,(G1="")+ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")`: Spells out that an empty search box shows every contact. SEARCH already treats empty text as a match, so the result here is the same.

## Common mistakes

- Join the columns with a space or another separator. Without one, the end of one column and the start of the next can form a false match.
- Excel has no live search-as-you-type for cells: the list updates when you press Enter in the search cell.
- In Google Sheets, FILTER has no third argument, so wrap it in IFNA for the "No results" message.

## Questions

**How do I search for two words at once?**

Test both and multiply: `ISNUMBER(SEARCH(G1,x))*ISNUMBER(SEARCH(H1,x))`, where x is the joined text.

**How do I make the search match whole cells only?**

Compare instead of searching: `=FILTER(A2:D10,(A2:A10=G1)+(B2:B10=G1)+(C2:C10=G1))`.

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/make-a-search-box