For Microsoft 365, use XLOOKUP with exact-match mode to find a key: if the lookup value is in E2, keys are in A2:A6, and return values are in B2:B6, enter =XLOOKUP(E2,A2:A6,B2:B6,"Not found",0). If E2 contains 104 and that key is in A5, the result is the value in B5. Exact matching is also available with VLOOKUP and INDEX/MATCH.
What an exact match means in Excel
An exact match asks Excel to find a value equal to the lookup value, rather than the nearest value above or below it. If a product code is 104, an exact lookup returns the row for 104; it should not return a result for 103 or 105 just because one of those values is nearby. This is usually the right behavior for IDs, names, order numbers, and other keys that identify specific records.
“Exact” describes the lookup rule, not necessarily every aspect of how Excel compares data. Standard lookups such as XLOOKUP and VLOOKUP are generally not case-sensitive: they treat “abc” and “ABC” as equivalent. They can also fail to match values that look alike but have different underlying types, such as the number 104 and the text string “104.” Spaces at the start or end of a value can cause a similar surprise.
Exact lookup is not the same as a search for a partial phrase. If you want to find a cell that contains a fragment, use a wildcard-enabled lookup or a text-search approach. For a strict ID lookup, avoid wildcard matching and use an exact-match mode.
Use XLOOKUP for a straightforward exact lookup
XLOOKUP is usually the clearest option in Microsoft 365. Its default match mode is exact, but specifying 0 makes the intention visible to anyone reading the formula. The lookup range and return range are separate, so the key does not have to be in the first column of a larger table. The two ranges should have the same number of rows or columns.
With IDs in A2:A6, names in B2:B6, and the ID to find in E2, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found",0). If E2 is 104 and A5 contains 104, the result is the name from B5, Dana. If E2 is 999 and that key is absent, the formula returns Not found instead of an error. The fourth argument supplies that custom missing-value result; the final 0 requests an exact match.
XLOOKUP searches from the first item to the last by default. If a key occurs more than once, it returns the first matching result. That behavior can conceal duplicate keys, so check whether the lookup column is supposed to be unique. XLOOKUP can also search in reverse when requested, but choosing the last duplicate does not resolve the underlying data-quality issue.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If someone opens the workbook in an older Excel version that does not support the function, they may see a #NAME? error. Use a compatible formula such as INDEX/MATCH or VLOOKUP when the file must work in older versions.
Use VLOOKUP when the key is in the first table column
VLOOKUP remains common in existing workbooks and is supported in many older Excel versions. Its fourth argument controls approximate versus exact matching. Use FALSE (or 0) for exact matching; leaving that argument out can allow approximate matching, which is risky for IDs and unsorted lists.
With IDs in A2:A6, product names in B2:B6, prices in C2:C6, and the ID to find in E2, use =VLOOKUP(E2,A2:C6,3,FALSE). If E2 is 104 and row 5 contains that ID, the formula returns the value in C5. The 3 means return the third column of the selected table range, and FALSE requests an exact match.
VLOOKUP has two important constraints. The lookup key must be in the leftmost column of the table range, and the return-column number is counted from the left edge of that range. Inserting or removing columns can make a hard-coded column number point somewhere unintended. VLOOKUP with FALSE can also treat the asterisk and question mark as wildcard characters in text lookup values; that is different from matching those symbols literally.
Use INDEX and MATCH for flexible older-workbook formulas
INDEX/MATCH is a useful alternative when XLOOKUP is unavailable or when a formula needs to look left as well as right. MATCH finds the position of a key, and INDEX returns the value at that position in another range. In MATCH, the third argument 0 means exact match.
With IDs in A2:A6, names in B2:B6, and the target ID in E2, enter =INDEX(B2:B6,MATCH(E2,A2:A6,0)). If E2 is 104 and the key is in the fourth position of A2:A6, the formula returns the fourth value in B2:B6, Dana. The lookup and return ranges need to align row by row.
Unlike XLOOKUP with a custom if-not-found argument, this formula returns #N/A when MATCH cannot find the key. You can handle that by wrapping the lookup in IFNA. With the same ranges and E2 value, use =IFNA(INDEX(B2:B6,MATCH(E2,A2:A6,0)),"Not found"); if 999 is absent, the result is Not found. IFNA handles #N/A specifically, while IFERROR also hides other formula errors, which may make genuine problems harder to spot.
Build and verify a lookup with a small example
A reliable lookup starts with a clear key column and a separate result column. For example, a small inventory list might have an item code in column A, a description in column B, and a stock count in column C. Put the code to find in E2 and decide whether the result should be the description or the stock count before writing the formula.
- Check that each key is stored consistently. Do not mix numbers and text versions of codes unless you have a reason to.
- Choose a lookup range containing only the keys and a return range containing the desired results. For XLOOKUP, these ranges can be in different columns.
- Use XLOOKUP with match mode 0, VLOOKUP with FALSE, or MATCH with 0.
- Try a key that exists and verify the returned value against the source row. Then try a key that does not exist and confirm the missing-result behavior.
- If the same key may appear more than once, determine whether the first result is acceptable and whether duplicates should be investigated.
For example, with item codes in A2:A6, stock counts in C2:C6, and a requested code in E2, use =XLOOKUP(E2,A2:A6,C2:C6,"Code not found",0). If E2 is 104 and C5 contains 18, the result is 18. This check is useful because a formula can be syntactically correct yet return an unexpected row when the source data contains duplicate codes or the wrong range was selected.
Fix common exact-match problems
A #N/A result means the lookup did not find a match under the formula’s comparison rules. Before changing the formula, compare the lookup value with the source value directly and inspect the data. A “not found” result is often a data-cleaning issue rather than a problem with exact-match mode.
- Number versus text: A numeric code may be stored as a number in one place and as text in another. Formatting a cell as Text does not always convert values already entered. Convert the source consistently, taking care with codes that have leading zeros, since converting those to numbers can remove meaningful zeros.
- Extra spaces: A leading or trailing space makes text values different. TRIM can remove ordinary extra spaces from text, but it does not remove every possible invisible character, such as a nonbreaking space copied from a web page.
- Different punctuation or characters: Hyphens, apostrophes, and visually similar characters can differ. Compare the original values and clean the source rather than assuming the cells are identical because they look similar.
- Wrong range: Confirm that the lookup range includes the key and the return range lines up with it. In VLOOKUP, confirm both the first-column requirement and the column index.
- Duplicate keys: XLOOKUP returns the first match by default. Sort order can therefore affect which duplicate appears first, although sorting does not make the key unique.
Standard exact lookups are not case-sensitive. If capitalization itself distinguishes keys, use a case-sensitive comparison. With candidate text values in A2:A5, corresponding results in B2:B5, and the case-sensitive value to find in E2, use =XLOOKUP(TRUE,EXACT(E2,A2:A5),B2:B5,"Not found",0). If E2 is “ABc” and A3 is exactly “ABc,” the formula returns B3; a value “abc” does not count as the same case-sensitive text. This array-based approach works in Microsoft 365.
Choose the right lookup method
For a new workbook in Microsoft 365, XLOOKUP is a strong default: it is readable, supports a custom not-found result, and does not require the return column to be to the right of the key. Use VLOOKUP when maintaining a workbook that already uses it or when compatibility with older Excel versions matters and the key is the first column in the selected table.
INDEX/MATCH is flexible and widely used in older workbooks, especially when a formula must return a value from a column to the left of the key. It takes more nesting than XLOOKUP, but the MATCH argument makes the exact-match setting explicit. For a quick check rather than a result from another column, Find (Ctrl+F on Windows; Command+F on Mac) searches displayed worksheet content, but it is not a reusable lookup formula and does not return a related value into a cell.
Do not use approximate matching simply because it appears to return a value. Approximate lookup is intended for ordered ranges such as grading bands or rate thresholds, not unique codes. If you need a nearest threshold, define the intended ordering and boundary rules, then verify the results with representative values.
Frequently Asked Questions
Does XLOOKUP use exact match by default?
Yes. In Microsoft 365, XLOOKUP defaults to exact matching. You can add 0 as its match_mode argument to make that choice explicit, which can help readers understand the formula.
How do I make VLOOKUP an exact match?
Set VLOOKUP’s fourth argument to FALSE or 0. For example, with the lookup value in E2 and the table in A2:C6, =VLOOKUP(E2,A2:C6,3,FALSE) searches for an exact key and returns the third table column.
Why does Excel return #N/A when the value looks the same?
The values may differ in type, spaces, punctuation, or hidden characters. Check whether one is text and the other numeric, and inspect for leading or trailing spaces. Also confirm that the lookup range contains the value you expect.
Is Excel exact match case-sensitive?
No. XLOOKUP, VLOOKUP, and ordinary MATCH lookups are not case-sensitive. Use EXACT with an array-based lookup, as shown above, if uppercase and lowercase must count as different.
Can exact match return more than one result?
A standard XLOOKUP returns one result: the first match by default. If keys are duplicated, remove or resolve duplicates when each key should be unique. If you need every matching row, use a filtering approach such as FILTER in Microsoft 365 or Excel 2021 and later.
Related
- Free Debt Payoff Calculator Excel: How to Build One
- DC Format in Excel: What It Means and How to Format Cells
- DBeaver Export to Excel: Steps, Options, and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter Highlighted Cells in Excel by Cell ColorTo filter highlighted cells in Excel, turn on filtering, open the drop-down arrow in the column that contains…
- Excel Formula: Keep a Cell Fixed When You Copy ItTo keep a cell reference fixed when copying an Excel formula, add dollar signs to it: with a…
- Excel COUNTIF Criteria: Rules, Examples, and FixesIn Excel, COUNTIF criteria are the conditions that tell the COUNTIF function which cells to count. The syntax…
- Basic VLOOKUP in Excel: Formula, Examples, and FixesIn Excel, VLOOKUP searches for a value in the leftmost column of a range and returns a value…
- Number Format in Concatenate Excel: Keep Dates & DecimalsExcel concatenation turns numbers into text, but it does not carry over the number format shown in the…
- DMAX Function in Excel: Syntax, Examples, and CriteriaExcel’s DMAX function returns the largest numeric value in a selected column of a database-like table, but only…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.