How to make a search box in Excel
Excel formula
=FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")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.
- Result on the example
- a 4-row list that spills from F3 in
F3 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in F3. Change any input and the result updates, or edit the formula to experiment.
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.
=FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")Result{4 rows × 4 cols} "Ana Ruiz", "Northwind", "Salt Lake City", "555-0141", …
A2:D109 rows × 4 cols: "Ana Ruiz", "Northwind", "Salt Lake City", "555-0141", …A range: 9 rows × 4 columns.
G1"lake"The value in G1.
A2:A109 rows × 1 col: "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", …A range: 9 cells in one column.
A2:A10&" "{9 rows × 1 col} "Ana Ruiz ", "Raj Patel ", "Lee Chen ", "Kim Ortiz ", …The & operator joins as text.
B2:B109 rows × 1 col: "Northwind", "Lakeside Dental", "Contoso", "Fabrikam", …A range: 9 cells in one column.
A2:A10&" "&B2:B10{9 rows × 1 col} "Ana Ruiz Northwind", "Raj Patel Lakeside Dental", "Lee Chen Contoso", "Kim Ortiz Fabrikam", …The & operator joins as text.
A2:A10&" "&B2:B10&" "{9 rows × 1 col} "Ana Ruiz Northwind ", "Raj Patel Lakeside Dental ", "Lee Chen Contoso ", "Kim Ortiz Fabrikam ", …The & operator joins as text.
C2:C109 rows × 1 col: "Salt Lake City", "Denver", "Boston", "Austin", …A range: 9 cells in one column.
A2:A10&" "&B2:B10&" "&C2:C10{9 rows × 1 col} "Ana Ruiz Northwind Salt Lake City", "Raj Patel Lakeside Dental Denver", "Lee Chen Contoso Boston", "Kim Ortiz Fabrikam Austin", …The & operator joins as text.
SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10){9 rows × 1 col} 25, 11, #VALUE!, #VALUE!, …SEARCH: Position of text, not case-sensitive, allows wildcards.- find_text
G1"lake" - within_text
A2:A10&" "&B2:B10&" "&C2:C10{9 rows × 1 col} "Ana Ruiz Northwind Salt Lake City", "Raj Patel Lakeside Dental Denver", "Lee Chen Contoso Boston", "Kim Ortiz Fabrikam Austin", …
- find_text
ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)){9 rows × 1 col} TRUE, TRUE, FALSE, FALSE, …ISNUMBER: TRUE if the value is a number.- value
SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10){9 rows × 1 col} 25, 11, #VALUE!, #VALUE!, …
- value
FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results"){4 rows × 4 cols} "Ana Ruiz", "Northwind", "Salt Lake City", "555-0141", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:D109 rows × 4 cols: "Ana Ruiz", "Northwind", "Salt Lake City", "555-0141", … - condition1
ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)){9 rows × 1 col} TRUE, TRUE, FALSE, FALSE, … - condition2, ...
"No results"
- range
In Google Sheets
Google Sheets needs a different formula: =IFNA(FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10))),"No results"). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10)),"No results")Excel only | spills 2 × 4Different result | 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")Excel only | spills 4 × 4 | 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.
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.
Make a search box
F3, return every contact whose name, company or city contains the text typed in G1, or "No results" when nothing matches.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)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.