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, Excel ships with at least 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 the game since 2020. 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 highest-ROI Excel skills you can build. It's also tested heavily on the MOS Excel Associate and Expert exams, so the techniques here pull double duty.
Press Ctrl+F on Windows or Command+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's the basic flow, and for 80% of searches it's all you need.
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. You can even select multiple rows in that list (Ctrl+click) to highlight all those cells at once, then format them in bulk.
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 and you can clean a dataset in seconds. 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. Watch out though — if the operation is irreversible because you saved after, you'll wish you'd worked on a copy. Always duplicate the sheet first when running mass replacements on production 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 way faster 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.
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 region, you could use =MID(A2, SEARCH("-", A2, 6)+1, SEARCH("-", A2, SEARCH("-", A2, 6)+1) - SEARCH("-", A2, 6) - 1). Verbose but reliable — it pulls the text between the second and third hyphens. With TEXTSPLIT in Excel 365 you can do it in one line, but on older versions FIND/SEARCH plus MID is still the workhorse.
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). For 95% of business work you want 0.
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". It outperforms VLOOKUP on large datasets and works in either direction.
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'. Common in data cleanup tasks where casing carries meaning, such as parsing camelCase product codes or extracting initials. 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 outperforms VLOOKUP on large datasets and works in either direction across columns.
Modern replacement for VLOOKUP/HLOOKUP/INDEX-MATCH. Searches a range and returns a corresponding value from another. Supports exact, approximate, and reverse search via the search_mode argument. Available in Excel 365 and Excel 2021. 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. Excel 365 and 2021 only. Combine conditions with * (AND) and + (OR) inside the include argument. Wrap with IFERROR to handle empty result sets gracefully.
If you're on Excel 365 or Excel 2021, 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 even supports approximate-and-binary matching with a single argument.
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 game-changer. 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. No more pressing Ctrl+Shift+Enter, no more array formula contortions. If you've been writing macros to dump filtered tables onto other sheets, FILTER replaces 50 lines of VBA with one cell.
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 performance tanks. 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 causes the most common cause of "my formula returns N/A but the value is right there" frustration.
Searching a million rows isn't free. Ctrl+F is fast because it uses Excel's native indexing, but Find All on a workbook-wide search can take 30 seconds on a slow machine. Filters are faster because they short-circuit on hidden rows. Formula-based searches — especially XLOOKUP without sorted data or MATCH with match_type 0 — scan the array linearly, so they slow down as your dataset grows. If you're hitting 100,000+ rows and performance matters, sort your lookup column and use approximate-match XLOOKUP with search_mode 2 (binary search). Done right, that's 100x faster.
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. Combine with Find All's bulk-select trick to recolor them all at once, or to delete them.
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.
Excel doesn't natively support regex in the Find dialog (Microsoft has hinted at it for years but it's still not shipped as of 2024). However, the new REGEXEXTRACT, REGEXTEST, and REGEXREPLACE functions were announced for Microsoft 365 in 2024 and started rolling out. Once they're in your build, you can do real pattern matching in formulas. Until then, the workaround is Power Query, which has had regex for years under M's Text.Select and Text.Replace functions, or VBA macros with the VBScript.RegExp object.
If your dataset is huge and you only want to search a portion, first select the range, then press Ctrl+F. The dialog respects your selection by default. This is invaluable when you have a 50-column sheet and only want to search a single column — select the column header, press Ctrl+F, and Excel won't waste cycles scanning the other 49 columns. The same trick works for Replace, MATCH, and any function that accepts a range argument.
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. Wrap with IFERROR to handle the no-results case gracefully: =IFERROR(..., "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. Filters in Power Query are case-sensitive by default, support multiple conditions, and most importantly 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 fast they can pull a value out of a 2-million-row workbook — and that's a question of which search tool you reach for first.
Outside of day-to-day work, the seven search techniques covered here appear directly on certification exams. The Microsoft Office Specialist (MOS) Excel Associate exam tests Find & Replace, basic filters, and the SEARCH and FIND functions. The MOS Expert exam goes further, asking about MATCH, INDEX-MATCH, XLOOKUP, and dynamic array functions including FILTER. Job interview tests at financial firms and analyst roles almost always include a timed lookup question — typically asking the candidate to extract a value from a multi-sheet workbook using XLOOKUP or INDEX-MATCH without help.
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. The fastest answer is usually a filter; the most reusable answer is usually a formula. Knowing both, and which to pick when, is what an interviewer is really testing for.