To find the position of a value, use MATCH with exact-match mode. With the value to find in E2 and the search list in A2:A100, enter =MATCH(E2,A2:A100,0); Excel returns the value’s position within that range, or an error if it is not there. If you need the matching value from another column, use a lookup formula such as XLOOKUP instead.
Choose a formula based on what you need
“Find a match” can mean several different things in a spreadsheet. You might want to know whether a value appears in a list, count how many times it appears, find its position, return information from the same row, or display every matching row. The right formula depends on the result you want, not just on the word “match.”
- Find a position: MATCH or XMATCH returns a number showing where the match occurs within a range.
- Check whether a value exists: COUNTIF counts matches; comparing that count with zero gives a simple TRUE or FALSE test.
- Return related information: XLOOKUP searches one range and returns a value from another.
- Return all matching rows: FILTER can produce a spill range containing every row that meets a condition.
For a straightforward exact lookup in current Excel, XLOOKUP is often the clearest choice when you need a related result. MATCH is more suitable when the position itself matters or when you are building a formula around that position. The examples below assume Microsoft Excel for Microsoft 365 on Windows unless a version note says otherwise.
Find a value’s position with MATCH
MATCH searches one row or one column and returns the relative position of the first matching item. The third argument controls how Excel matches: 0 means exact match, 1 means an approximate match against an ascending list, and -1 means an approximate match against a descending list. Use 0 for ordinary lists of names, IDs, or other values where the entry must be identical.
For example, with the lookup value “P-204” in E2 and IDs in A2:A6, if P-204 is in A4, the formula =MATCH(E2,A2:A6,0) returns 3. It returns 3 because A4 is the third cell in the searched range, not because A4 is row 3 on the worksheet. If the range began at A10, the result would still be a position relative to the selected range.
MATCH does not return the contents of the matched cell; it returns a position. That distinction is useful when another formula needs to use the position. If the value is absent, MATCH returns #N/A, which is Excel’s standard signal that it could not find the requested item.
Use XMATCH for a modern position lookup
XMATCH is a newer alternative to MATCH, available in Microsoft 365 and Excel 2021 or later. It returns a position and uses exact matching by default, so a basic formula can be shorter. With the search value in E2 and the list in A2:A100, =XMATCH(E2,A2:A100) returns the position of an exact match.
XMATCH also offers options for wildcard matching and search direction. Its optional match-mode argument can request exact matching, an exact match or next smaller/larger item, or wildcard matching. Its search-mode argument can search from first to last or last to first. Those options can be useful with duplicates or ordered data, but for a normal ID list, the default exact, first-to-last behavior is usually appropriate.
XMATCH and MATCH are not interchangeable in every older workbook: people using Excel versions before Excel 2021 may not have XMATCH. If a workbook must work across older installations, MATCH with its third argument set to 0 is a broadly compatible exact-match option.
Check whether two lists contain the same items
To flag whether each item in one list appears in another, COUNTIF is a simple choice. With values to check in A2:A100 and the reference list in B2:B100, enter =COUNTIF($B$2:$B$100,A2)>0 beside the first value and fill the formula down. For an item found once or more, the result is TRUE; if it is absent, the result is FALSE. The dollar signs keep the reference list fixed as the formula is copied.
If you want a count rather than a TRUE/FALSE answer, remove the comparison with zero. With the lookup value in A2 and the list being checked in B2:B100, =COUNTIF($B$2:$B$100,A2) returns the number of occurrences. For instance, if the value in A2 appears twice in that list, the result is 2. This is useful for finding duplicates as well as checking membership.
COUNTIF is not case-sensitive: it treats “abc” and “ABC” as a match. It also recognizes wildcard characters in criteria. An asterisk represents any number of characters and a question mark represents one character, so a criterion can intentionally match a pattern rather than a whole literal value. If the text being searched may itself contain wildcard characters, account for that when constructing the criterion.
Return a value from the matching row
When you need a result associated with an ID, XLOOKUP can search one column and return the value from another. Suppose product IDs are in A2:A100, prices are in C2:C100, and the ID to find is in E2. The formula =XLOOKUP(E2,A2:A100,C2:C100,"Not found",0) searches the ID column and returns the corresponding price. If no ID matches, it displays “Not found.” The final 0 specifies exact matching.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel editions. It returns the first matching result by default; if the lookup column contains repeated IDs, it does not automatically return every price. Check that the lookup and return ranges cover corresponding rows—for example, A2:A100 should align with C2:C100—so the returned value comes from the intended record.
For older Excel versions, INDEX and MATCH can perform a similar lookup. With IDs in A2:A100, prices in C2:C100, and the ID to find in E2, =INDEX(C2:C100,MATCH(E2,A2:A100,0)) returns the price from the row where MATCH finds the ID. This combination works in older versions too, and it can return a value from a column to either side of the lookup column, unlike VLOOKUP’s usual left-to-right arrangement.
Return every matching row
If you need all records that meet a condition rather than just the first result, FILTER is designed for that task. FILTER is available in Microsoft 365 and Excel 2021 or later. With a table in A2:C100, the product IDs in A2:A100, and the requested ID in E2, =FILTER(A2:C100,A2:A100=E2,"No matches") returns every row whose ID equals E2. If none qualify, it displays “No matches.”
The results spill into cells below and to the right of the formula, so those cells must be empty. If something blocks the output area, Excel may show #SPILL!. Make room for the results or move the formula. FILTER is especially useful for lists with duplicates because it can show all matching records, whereas XLOOKUP returns one result by default.
Highlight matching values without a helper column
A formula is not the only way to make matches visible. To highlight entries from one list that also appear in another, select the cells to format, then choose Home > Conditional Formatting > New Rule and select “Use a formula to determine which cells to format.” With A2:A100 selected as the range to highlight and the comparison list in B2:B100, use =COUNTIF($B$2:$B$100,A2)>0 as the rule formula, choose a format, and confirm it.
The formula’s relative reference A2 adjusts for each selected row, while the dollar signs keep the comparison range fixed. If the selected range starts at A5 instead, the formula should refer to A5 so the first row is evaluated correctly. Conditional formatting changes how cells appear; it does not remove duplicates or create a separate list of matching records.
Fix common match problems
An exact-match formula can return #N/A even when two entries look alike. The underlying values may differ because one contains an extra space, a nonbreaking space copied from a web page, or a different data type. For example, a number stored as text may not behave like a numeric value. Compare the source cells, check number formats and clean imported data before concluding that the item is absent.
Other practical checks include:
- Verify the ranges: Make sure the lookup range includes the expected value and that the return range covers the same rows.
- Use exact mode for ordinary lists: In MATCH, include 0 as the third argument. Approximate matching can give unexpected results when the list is not sorted as required.
- Check duplicates: MATCH and XLOOKUP return the first match under their normal search settings. Use COUNTIF to count occurrences or FILTER to display all matching rows.
- Inspect copied formulas: Use dollar signs on ranges that should stay fixed when filling a formula down or across.
- Distinguish errors from no matches: #N/A means a lookup did not find a result; #SPILL! with FILTER usually means its output cells are blocked.
If you prefer a message to #N/A, wrap MATCH in IFNA. With the lookup value in E2 and the list in A2:A100, =IFNA(MATCH(E2,A2:A100,0),"No match") returns the position when found and “No match” when not found. Use this when a missing value is an expected outcome; keeping the error visible can be more useful while diagnosing an unexpected mismatch.
Frequently Asked Questions
What is the simplest Excel formula to find an exact match?
With the value to find in E2 and the search list in A2:A100, use =MATCH(E2,A2:A100,0). The final 0 requests an exact match, and the result is the item’s position within the range.
How do I return a value next to the match?
Use XLOOKUP when available: search the column containing the key and specify the corresponding result column. For older Excel editions, INDEX combined with MATCH is a compatible alternative.
How can I find matches between two columns?
Use COUNTIF beside the first list to test each item against the second list, then fill the formula down. A result greater than zero means the item occurs in the comparison range.
Why does Excel show #N/A when the value appears to be present?
Check for extra spaces, text-versus-number differences, hidden characters, and an incorrect search range. Exact matching requires the stored values to correspond, even when formatting makes them look similar.
Can Excel show all matches instead of only the first one?
Yes. In Microsoft 365 or Excel 2021 and later, FILTER can return all rows that meet a condition. Ensure the cells where the results will spill are empty.
Related
- Data Validation Interview Questions and Strong Answers
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUMIF Formula Examples for Everyday TasksExcel’s SUMIF function adds values that meet one condition. With regions in A2:A7 and sales amounts in B2:B7,…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
- Excel Formula to Remove Duplicates From One ColumnIn Excel for Microsoft 365 or Excel 2021, enter =UNIQUE(A2:A100) in an empty cell to return a live…
- Excel Formula for Age From Date of BirthThe simplest Excel formula for age from a date of birth is =DATEDIF(A2,TODAY(),"Y"), assuming the birth date is…
- Excel Text Join With Line Breaks: Formulas and TipsTo join text with a line break in Excel for Microsoft 365, use TEXTJOIN with CHAR(10) as the…
- Excel Filter Shortcut Keys: Apply, Open, Clear, and UseIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for a data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.