For an exact match in a list, use =MATCH(E2,A2:A100,0): it returns the matching item’s position within the range, or #N/A if no match is found. If you want Excel to return related information instead, use XLOOKUP, such as a formula that finds an ID and returns the person’s name.
What the MATCH formula returns
MATCH searches one row or one column for a value and tells you where it appears in that range. It returns a position, not the value itself. The final zero in the formula requests an exact match; including it avoids relying on approximate-match behavior.
Assume IDs are in A2:A6, with 104, 107, 112, 115, and 120 in that order, and the ID to find is in E2, where E2 contains 112. Use:
=MATCH(E2,A2:A6,0)
The result is 3 because 112 is the third item in A2:A6. It is on worksheet row 4, but MATCH returns 3 because it counts from the start of the selected range. If E2 contains 999, the result is #N/A, meaning there is no exact match in the range.
This distinction matters when you use MATCH inside another formula. Its position can identify which row or column contains a result, but it does not by itself return the corresponding name, price, or other field. For a simple search where you need the associated value, a lookup function is usually more convenient.
Return information from the matching row with XLOOKUP
XLOOKUP searches one range and returns the value at the corresponding position in another range. It is often the clearest choice when you know the lookup value and want a related result. It can search in either direction and uses exact matching by default, though stating the match mode explicitly makes the intent visible.
Assume IDs are in A2:A6, names are in B2:B6, and E2 contains 112. If the name in B4 is Maya Chen, use:
=XLOOKUP(E2,A2:A6,B2:B6,"Not found",0)
The formula returns Maya Chen. The lookup range and return range are aligned: the third item in A2:A6 corresponds to the third item in B2:B6. The text “Not found” is the result to show when there is no match, and the final zero specifies exact matching.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The function works in current Excel for Windows, Mac, and the web when the version supports it. In older Excel versions, use INDEX and MATCH together instead.
Assume IDs are in A2:A100, names are in B2:B100, and E2 holds the ID to find. This compatible alternative returns the name from the aligned row:
=INDEX(B2:B100,MATCH(E2,A2:A100,0))
If E2 contains 112 and the corresponding name is Maya Chen, the result is Maya Chen. INDEX returns the item at a position, while MATCH supplies the position of the exact ID.
Check whether values in two lists match
Sometimes the goal is not to return a name or position; it is to flag whether each item in one list also appears in another. COUNTIF can count occurrences of a value in a second list. Comparing that count with zero gives a simple TRUE or FALSE result.
Assume the first list is in A2:A6, the list to check against is in B2:B100, and A2 contains 107. Enter this formula beside the first item and fill it down:
=COUNTIF($B$2:$B$100,A2)>0
The formula returns TRUE if 107 appears anywhere in B2:B100 and FALSE if it does not. The dollar signs keep the comparison range fixed as you copy the formula down, while A2 changes to A3, A4, and so on. If duplicates matter, COUNTIF can also show how many times an item occurs rather than just whether it occurs.
For a row-by-row comparison, where the value in column A should be checked against the value beside it in column B, a direct equality test is simpler. Assume A2 and B2 contain the two values to compare:
=A2=B2
This returns TRUE when the values are equal and FALSE otherwise. It compares only those two cells; it does not search the full second list. Choose COUNTIF for “does this appear anywhere?” and a direct equality test for “does this cell equal the cell beside it?”
Choose the formula that fits the task
The phrase “match values” can describe several different jobs. Pick a formula based on what the result should be, not just on the word “match.”
- Find a position: Use MATCH when you need the item’s position within a row or column, especially as part of a larger formula.
- Return related data: Use XLOOKUP when you want a corresponding name, amount, category, or other value and your Excel version supports it.
- Test membership: Use COUNTIF when you need to check whether each value in one list appears in another.
- Compare two cells: Use an equality test when the values are already side by side and you do not need a search.
- Support older workbooks: Use INDEX with MATCH if XLOOKUP is unavailable or compatibility with older Excel is important.
For a one-off search, the shortest formula is not always the easiest to maintain. A clearly labeled helper column that says “Found” or “Missing” can make a list check easier for coworkers to review. For a report that needs the matching record’s details, a lookup formula avoids manually locating the row.
Exact, approximate, wildcard, and case-sensitive matches
For most IDs, email addresses, product codes, and names, exact matching is the right starting point. With MATCH, set the third argument to zero. With XLOOKUP, exact matching is the default, but adding a zero as the match-mode argument documents the choice. Approximate matches are useful for tasks such as assigning a rate based on thresholds, but they require careful setup: MATCH with a match type of 1 expects ascending order, while a match type of -1 expects descending order. Unsorted data can produce misleading results.
Text matching with MATCH in exact mode can treat the asterisk (*) and question mark (?) as wildcards. An asterisk represents any sequence of characters, and a question mark represents one character. A tilde can escape a wildcard when you need to search for the symbol itself. XMATCH uses exact matching by default and has a separate wildcard match mode, so check its match-mode argument before expecting wildcard behavior.
Standard Excel comparisons are generally not case-sensitive: “North” and “north” count as the same text for ordinary matching. If capitalization must matter, use the EXACT function to compare two cells. Assume A2 contains North and B2 contains north:
=EXACT(A2,B2)
The result is FALSE because the capitalization differs. EXACT compares the text in two cells and is case-sensitive; it does not search a whole list on its own.
Fix common matching problems
The most common result that surprises people is #N/A. It usually means Excel did not find an exact match, but the cause may be less obvious than a genuinely missing value. Check that the target value is in the intended range, that the range covers all relevant rows, and that the formula uses exact matching where appropriate.
- Numbers stored as text: A numeric-looking value stored as text may not match a true number. Check the source cells’ data types and convert consistently rather than changing only the lookup formula.
- Extra spaces: Leading or trailing spaces can make two text values differ. TRIM can remove ordinary extra spaces from text, but cleaning the source data is often easier to audit than embedding cleanup in a complicated lookup.
- Different characters: A hyphen, nonbreaking space, or visually similar character can differ from the one in the other list. Copying a suspect value into a plain-text editor may help reveal unexpected characters.
- Misaligned ranges: In a lookup, the lookup and return ranges must correspond item by item. A shifted return range can produce a valid-looking but incorrect result.
- Approximate matching on unsorted data: If you request approximate matching, verify that the lookup range is sorted in the direction required by the chosen mode.
If you prefer a readable message rather than an error when a match is missing, wrap MATCH in IFNA. Assume IDs are in A2:A100 and the value to find is in E2:
=IFNA(MATCH(E2,A2:A100,0),"Not found")
If E2 is absent, the formula displays Not found; if it is present, it returns its position. IFNA handles the specific #N/A result, while IFERROR catches a wider range of errors. Use the narrower option when a missing match is the only error you intend to replace, since other problems should remain visible.
Version and compatibility notes
MATCH, INDEX, COUNTIF, EXACT, IFNA, and XLOOKUP are distinct functions with different roles. XLOOKUP and XMATCH are available in Microsoft 365 and Excel 2021 or later; a workbook that uses them may not calculate correctly in an older Excel release. If you share a workbook with people using older software, INDEX and MATCH are broadly compatible alternatives for returning a related value.
The examples use commas between function arguments, as in the standard English-language syntax. Some regional Excel settings use semicolons as argument separators; if Excel rejects an otherwise valid formula, your system’s list-separator setting may be the reason. Formula names and the main matching behavior are consistent in current desktop Excel for Windows and Mac, and Excel for the web supports these functions when they are available in the account’s Excel version.
Frequently Asked Questions
What is the simplest Excel formula to find an exact match?
Use MATCH with zero as its third argument. It returns the position of the exact value within the range, rather than returning the value itself.
How do I return a value from the same row as a match?
Use XLOOKUP with a lookup range and a corresponding return range. In older Excel versions, INDEX combined with MATCH can return the value from the aligned row.
Why does Excel show #N/A when the value is visible?
The visible entries may differ because one is stored as text, contains extra spaces, uses a different character, or falls outside the formula’s selected range. Compare the underlying values and check the ranges before changing the match mode.
Does MATCH find partial text?
MATCH with exact-match mode can use wildcard characters in text searches, including * and ?. If you need a literal asterisk or question mark, account for wildcard escaping; XMATCH uses a separate wildcard mode.
Can Excel match values without regard to capitalization?
Yes. Ordinary matching is generally case-insensitive. Use EXACT when the capitalization itself must match, while remembering that EXACT compares two cells rather than searching a list.
Related
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LET and IF: Combine Them in Clearer FormulasLET and IF work together by assigning names to values or calculations, then using IF to choose a…
- How to Lock a Filter in Excel Without Losing ControlTo keep an Excel filter in place while protecting the worksheet, set up the filter first, then choose…
- Conditional Formatting Formula If Statement in ExcelIn Excel, a conditional-formatting formula must evaluate to TRUE or FALSE for the cell or row you want…
- Excel Time Formulas for Calculating Hours WorkedTo calculate hours worked in Excel, subtract the start time from the end time and multiply by 24;…
- SUMIF Function Examples: Text, Numbers, Dates in ExcelExcel’s SUMIF function adds numbers that meet one condition. With product names in A2:A6 and sales in B2:B6,…
- Split Text in Excel Without a Formula: Easy MethodsYes. In Excel for Microsoft 365 on Windows, split text without a formula using Data > Text to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.