To search in Excel, press Ctrl+F (Control+F on Mac) to open the Find dialog, type your text, and press Enter; press Ctrl+H to find and replace. For results you can use in calculations, use functions such as FIND, SEARCH, MATCH, XLOOKUP, or FILTER.
Searching a worksheet feels simple until you open a file with 80,000 rows and realize that Ctrl+F alone won't get you there. The truth is, this guide covers seven distinct ways to find data, and each one solves a different problem. Some are made for quick one-off lookups, others for pulling values into formulas, and a few for spotting patterns across whole tables.
This guide walks through every method, in order of complexity. You'll see when to use Find & Replace, when filters beat searching, how the FIND and SEARCH functions differ (yes, they're different), and how modern tools like XLOOKUP and FILTER have changed how searching works. We'll cover keyboard shortcuts, wildcards, case sensitivity, and the small traps that catch even experienced users — like why your search returns zero results when the value is clearly there.
Whether you're prepping for the Excel certification exam, sitting an interview test, or just trying to clean up a quarterly report, knowing how to search well is one of the most useful Excel skills you can build.
Press Ctrl+F on Windows or Control+F on Mac. A small dialog opens. Type what you want to find, hit Enter, and Excel jumps to the first match. Press Enter again to cycle through subsequent hits. That is the basic flow.
Click Options to expand the dialog and you'll see settings that most people never touch. Match case forces a case-sensitive search — useful when you're hunting for a specific product code that's case-mixed. Match entire cell contents only returns hits where the whole cell equals your query, not partial strings.
The Within dropdown lets you search a single sheet or the entire workbook, and Look in toggles between values (what you see) and formulas (the underlying calculation). That last one matters: if you're searching for a function name, you need to set Look in to Formulas, otherwise Excel will ignore it.
In the same Ctrl+F dialog, click Find All instead of Find Next. Excel lists every match in a panel below — sheet name, cell reference, value, and formula. Click any row and you jump to that cell. This is gold for auditing: instead of stepping through one match at a time, you see the entire footprint of your search term in one view. Selecting entries in that list selects the matching cells.
Use * to match any number of characters and ? to match exactly one character. Searching for Jo*n finds John, Jordan, Jonathon. Searching for ?at finds bat, cat, hat but not boat. To search for a literal asterisk or question mark, prefix it with a tilde: ~*. Wildcards work in the Find dialog and most lookup functions, but FIND treats them as literal characters — use SEARCH instead when you need wildcard support inside a formula.
Replace works the same as Find but with an extra field for the substitution. The killer feature is Replace All, which swaps every occurrence at once. Combine it with wildcards to clean a dataset quickly. Stripping the prefix "SKU-" from every product code? Find SKU-, replace with nothing, click Replace All. Done.
A trick most people miss: leave the Replace with field empty to delete characters in bulk. Need to remove every space in a column? Find (a single space), replace with empty, hit Replace All. Because Replace All changes every match at once, work on a copy of the sheet first when running mass replacements on important data.
Sometimes you don't need to find one value — you need to see all rows that match a condition. That's what filters are for. Select your data, press Ctrl+Shift+L to toggle filter arrows, then click any column header dropdown. You'll see Text Filters, Number Filters, or Date Filters depending on the column's content. Pick "Contains" and type a substring; Excel hides every row that doesn't match.
For more advanced filtering, type into the search box at the bottom of the dropdown. This searches the unique values in that column, which is quicker than scrolling. Tick the values you want to keep and click OK. To clear: same shortcut, or click the funnel icon and choose Clear Filter.
Use Ctrl+F. Fastest path to a single known value across the active sheet or workbook.
Use Ctrl+H. Edits hundreds of cells at once with optional wildcards.
Use AutoFilter (Ctrl+Shift+L). Best when you want to see every match in context.
Use FIND, SEARCH, MATCH, or XLOOKUP. Required when results feed into another calculation.
| Method | Shortcut or syntax | Scope | What it does |
|---|---|---|---|
| Find (Find tab) | Ctrl+F (Control+F on Mac) | Within: Sheet or Workbook (Selection in Excel for the web) | Finds text or numbers. Find Next steps through matches; Find All lists every match so you can click to jump to it. |
| Replace (Replace tab) | Ctrl+H (Control+H on Mac) | Same Within choices as Find | Replace changes one occurrence at a time; Replace All changes every occurrence that matches. |
| Options: Match case | Options button in the dialog | Applies to the current search | Limits matches to the same capitalization you typed. |
| Options: Match entire cell contents | Options button in the dialog | Applies to the current search | Finds only cells that contain exactly the characters in Find what. |
| Options: Format / Choose Format From Cell | Options button in the dialog | Applies to the current search | Searches by cell formatting, alone or together with a value. |
| Wildcards in Find | ? = one character, * = any number of characters, ~ = literal ? or * | Find and Replace dialog | ~ before ? or * finds the character itself. |
| FIND function | =FIND(find_text, within_text, [start_num]) | Inside one text string | Case-sensitive; does not allow wildcards; returns a position number. |
| SEARCH function | =SEARCH(find_text, within_text, [start_num]) | Inside one text string | Not case-sensitive; allows ? and * wildcards; returns a position number. |
| XLOOKUP wildcards | match_mode 2 | A lookup range | Turns on wildcard matching where *, ?, and ~ have special meaning. |
Sources: Microsoft Support, "Find or replace text and numbers on a worksheet", "SEARCH function" and "XLOOKUP function".
This is where many users get confused. Both functions look for a substring inside another string, both return the starting position as a number, and both throw a #VALUE! error if the substring isn't found. But they differ in two ways. FIND is case-sensitive; SEARCH is not. And SEARCH supports wildcards, while FIND does not.
Syntax is identical: =FIND(find_text, within_text, [start_num]) and =SEARCH(find_text, within_text, [start_num]). The optional third argument tells Excel where to begin looking. If you omit it, the search starts at position 1.
Practical example. Cell A2 holds "Order-2024-North-1245". To extract the first part, use =LEFT(A2, SEARCH("-", A2)-1), which returns Order. To pull the region, use =MID(A2, 12, SEARCH("-", A2, 12)-12), which starts at character 12 and stops at the next hyphen, returning North. FIND and SEARCH plus LEFT, MID, or RIGHT work in every Excel version.
If you need to find where a value lives in a range — not what's inside it — use MATCH or its newer cousin XMATCH. The classic form is =MATCH(lookup_value, lookup_array, [match_type]). Match type 0 means exact match; 1 means largest value less than or equal to (requires ascending order); -1 means smallest value greater than or equal (requires descending). Use 0 when you need an exact match.
Returns a position number. Combine with INDEX and you have the legacy lookup combo: =INDEX(B:B, MATCH("Widget", A:A, 0)). This pulls the value from column B on the same row where column A equals "Widget". Unlike VLOOKUP, it can return a value from a column to the left of the lookup column.
Case-sensitive search inside a string. Returns position number. No wildcards. Syntax: =FIND(find_text, within_text, [start_num]). Use when case matters, e.g., distinguishing 'Apple' from 'apple'. Throws #VALUE! if not found, so wrap in IFERROR for safety.
Case-insensitive search inside a string. Returns position number. Supports * and ? wildcards. Syntax: =SEARCH(find_text, within_text, [start_num]). The everyday choice when case is unimportant. Particularly powerful with wildcards — SEARCH("abc*xyz", A1) finds the pattern abc...xyz with any text between. Like FIND, returns #VALUE! when no match exists.
Finds the position of a value in a one-dimensional range. Returns position number, not the value itself. Pair with INDEX for two-way lookups. Match_type 0 = exact, 1 = approximate ascending, -1 = approximate descending. INDEX-MATCH can look up a value in a column to the left of the lookup column.
Modern replacement for VLOOKUP/HLOOKUP/INDEX-MATCH. Searches a range and returns a corresponding value from another. Exact match is the default; match_mode adds approximate and wildcard matching, and search_mode adds reverse and binary search. Available in Microsoft 365, Excel 2024 and Excel 2021, but not Excel 2016 or 2019. Built-in if_not_found argument eliminates IFERROR wrappers, making formulas much cleaner.
Dynamic array function that returns every row matching one or more conditions. Spills results into adjacent cells automatically. Replaces clunky array formulas. Available in Microsoft 365, Excel 2024 and Excel 2021. Combine conditions with * (AND) and + (OR) inside the include argument. Use the optional if_empty argument to handle empty result sets.
If you're on Excel 365 or Excel 2021 or 2024, the new dynamic array functions transform how you search. XLOOKUP finally fixes VLOOKUP's biggest weaknesses: it can search left as well as right, defaults to exact match, returns a custom value when nothing's found, and supports approximate matching (match_mode) and binary search (search_mode).
Basic syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Example: =XLOOKUP("North", A2:A100, C2:C100, "Region not found"). That formula reads cleanly even to someone who's never seen it before — try saying the same about a nested INDEX-MATCH.
FILTER is the other major advantage. Instead of returning a single value, it returns every row that meets your criteria. =FILTER(A2:D100, B2:B100="Active") pulls every row where column B equals "Active". The result spills down and across automatically. Use its optional third argument, if_empty, to show a message when nothing matches.
Ctrl+F has a setting buried in Options called Within. Change it from Sheet to Workbook and Excel searches every tab in the file. This is essential when you've inherited a workbook with 30 sheets and you have no idea which one contains the value you need. The result list from Find All shows the sheet name, so you can navigate straight to the hit without opening tabs one by one.
For formula-driven cross-sheet searching, you can extend MATCH or XLOOKUP across sheets with INDIRECT, but this can be slow on large workbooks. A cleaner pattern in modern Excel is to consolidate the sheets into one table with Power Query, then search the consolidated table. Power Query lives under Data > Get Data and is the right tool when your search problem has grown beyond a single sheet.
Wildcards behave differently depending on where you use them. In the Find dialog, asterisk and question mark always work. In SEARCH, they work. In FIND, they're treated as literal characters. In MATCH with match_type 0, they work. In VLOOKUP/HLOOKUP with exact match (FALSE/0), they work. In XLOOKUP, you have to enable them by setting match_mode to 2. Mixing this up is a common reason a formula returns #N/A even though the value is right there.
A worksheet can hold up to 1,048,576 rows, and searches over very large ranges take longer. Limit a search to the cells you need by selecting them first. For formulas, XLOOKUP supports binary search through search_mode 2 (ascending) or -2 (descending), but Microsoft notes that invalid results are returned if the data is not sorted, so only use it on sorted lookup columns.
This one trips most people up because it's hidden behind a small button. Open Ctrl+F, click Options, and you'll see a Format... button. Click it and you can search for cells with a specific font color, fill color, bold style, or number format — with or without value criteria. Need to find every cell highlighted in yellow? Set the format filter to yellow fill, leave the find field blank, hit Find All. Excel returns every yellow cell in the sheet.
You can also use "Choose Format From Cell" to copy the format of an existing cell as your search template. Click that button, click any cell in your sheet, and Excel grabs its formatting as the filter. This is faster than configuring the format dialog manually when you have a sample cell to point at.
The Find dialog supports only wildcards (*, ?, and ~), not regular expressions. However, Microsoft 365 now includes the REGEXEXTRACT, REGEXTEST, and REGEXREPLACE functions, which let you do real pattern matching in formulas. They are not available in older perpetual versions of Excel, so use wildcards with SEARCH there.
If your dataset is huge and you only want to search a portion, first select the range, then press Ctrl+F. This limits the search to that selection, which is handy on a wide sheet when you only want to search one column: select the column header, then press Ctrl+F.
For dashboards or reports you reuse, build a search experience directly into the worksheet. Drop a cell at the top, label it Search:, name it SearchTerm. Then use FILTER below: =FILTER(DataTable, ISNUMBER(SEARCH(SearchTerm, DataTable[Product]))). As the user types, the matching rows appear below. No macros, no dialogs, no clicks beyond typing.
You can extend this with multiple search columns by combining conditions with the * operator (acts as AND) or the + operator (acts as OR) inside FILTER. Example: =FILTER(DataTable, (ISNUMBER(SEARCH(SearchTerm, DataTable[Product]))) + (ISNUMBER(SEARCH(SearchTerm, DataTable[SKU])))) searches both Product and SKU columns at once. To handle the no-results case, add FILTER's third argument: =FILTER(..., ..., "No matches").
When your data lives across files, folders, or external systems, Power Query is the answer. Under Data > Get Data, you can load a CSV, Excel file, database, or web source into the Query Editor, then add filter steps that act like supercharged searches. Filter steps in Power Query support multiple conditions and are recorded as steps you can re-run on refreshed data. If you find yourself doing the same search every Monday morning, that's a sign to move it into Power Query and click Refresh instead.
Searching in Excel isn't one skill — it's a stack of seven skills that overlap. Master Ctrl+F for navigation, Ctrl+H for editing, filters for visual analysis, FIND/SEARCH/MATCH for legacy formulas, and XLOOKUP/FILTER for modern work. Know when to reach for each. Practice the wildcards and the format-based variants. The difference between a junior analyst and a senior one often comes down to how quickly they can pull the right value out of a large workbook, and that depends on which search tool you reach for first.
The seven search techniques covered here are everyday skills for anyone who works with large worksheets, and they are useful when preparing for Excel-based assessments too.
If you're preparing for any Excel-based assessment, drill these techniques until they're muscle memory. Open a sample workbook, hide the answer, and time yourself finding it three ways: Ctrl+F, a filter, and a formula. Filters are good for seeing matches in context; formulas are best when results feed other cells. Knowing both, and which to pick when, is what makes searching fast in practice.