With the value to find in E2, lookup values in A2:A6, and results in B2:B6, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found",0) for an exact match in Excel for Microsoft 365. The final 0 specifies exact matching; XLOOKUP also uses exact matching by default when you omit its match-mode argument.
What an exact match means in Excel
An exact match means Excel looks for a value that equals the lookup value, rather than choosing the nearest smaller or larger value. For example, searching for the product code P-104 should find P-104—not P-103 or P-105. This matters for product IDs, employee numbers, invoice references, category labels, and other values where a near match could be wrong.
Exact does not necessarily mean case-sensitive. XLOOKUP, VLOOKUP, and MATCH normally treat uppercase and lowercase letters as equivalent, so they consider “abc” and “ABC” a match. Nor does exact lookup automatically clean up extra spaces or convert text-formatted numbers into numbers. Excel compares the stored values according to its normal comparison rules; inconsistent data can therefore cause an unexpected “not found” result.
In a lookup formula, there are usually three key pieces: the value being searched for, the range containing possible matches, and the range containing the result to return. For the example above, E2 contains the search value, A2:A6 contains product codes, and B2:B6 contains prices. If A2:A6 contains P-101, P-102, P-104, P-107, and P-110, and B2:B6 contains 12.50, 16.00, 18.50, 21.00, and 25.00, searching for P-104 returns 18.50.
Use XLOOKUP for a straightforward exact lookup
XLOOKUP is generally the clearest choice in current Excel. Its basic pattern is lookup value, lookup array, return array, optional value if not found, and optional match mode. Using 0 for match mode makes the exact-match requirement explicit. The optional “Not found” text also gives a readable result when the search value is absent instead of displaying the #N/A error.
For the product table described above—with the search code in E2, codes in A2:A6, and prices in B2:B6—the formula returns 18.50 when E2 contains P-104: =XLOOKUP(E2,A2:A6,B2:B6,"Not found",0). XLOOKUP returns the first matching result it encounters. It can look in a return range to the left or right of the lookup range, unlike VLOOKUP, and it does not require a column index number.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If a workbook will be opened in an older Excel release that does not support XLOOKUP, use a compatible alternative such as VLOOKUP or INDEX with MATCH. Excel for the web and Excel for Mac also support XLOOKUP in Microsoft 365, though interface details can differ; the formula itself works the same way.
Use VLOOKUP when working with older workbooks
VLOOKUP can perform an exact lookup when its fourth argument is FALSE. The lookup value must be in the first column of the table array, and the column index tells Excel which column in that array should supply the result. Include FALSE explicitly: leaving out the fourth argument can lead to approximate matching, which is not appropriate for many IDs or labels.
With product codes in A2:A6, prices in B2:B6, and the requested code in E2, this formula returns 18.50 for P-104: =VLOOKUP(E2,A2:B6,2,FALSE). The 2 means return a value from the second column of A2:B6; FALSE requests an exact lookup. If the lookup column is not the leftmost column in the table, VLOOKUP cannot look to its left. Rearrange the data or choose XLOOKUP or INDEX/MATCH instead.
VLOOKUP is widely supported, including in older Excel versions. However, inserting or removing columns inside the table can make a hard-coded column number point to a different result column. It is also normally case-insensitive. For basic compatibility, it remains useful; for a new Microsoft 365 workbook, XLOOKUP is often easier to read and maintain.
Find a match’s position with MATCH or XMATCH
Sometimes you need to know where an item appears, not return a related value. MATCH returns the relative position of a match within a range. Its third argument should be 0 for an exact match. A result of 3 means the match is the third cell in the selected range, not necessarily row 3 of the worksheet.
With the search value in E2 and product codes in A2:A6, this formula returns 3 if P-104 is the third item in that range: =MATCH(E2,A2:A6,0). The 0 is important because MATCH can use other match types; those modes have ordering requirements and can return approximate results.
XMATCH is a newer alternative with exact matching as its default. You can still specify 0 to make the intent clear. With E2 holding P-104 and the codes in A2:A6, this formula returns 3: =XMATCH(E2,A2:A6,0). XMATCH is available in Microsoft 365 and Excel 2021 or later. MATCH is the safer choice if a workbook needs to support much older versions.
To return a related value using MATCH, combine it with INDEX. With P-104 in E2, codes in A2:A6, and prices in B2:B6, the following formula returns 18.50: =INDEX(B2:B6,MATCH(E2,A2:A6,0)). MATCH identifies the position, and INDEX returns the value at that position in the price range. This combination can look in either direction and is compatible with older Excel versions.
Exact lookup is not always case-sensitive
A frequent misconception is that “exact match” means uppercase and lowercase must be identical. In standard XLOOKUP, VLOOKUP, MATCH, and XMATCH lookups, they do not. If a code system distinguishes ABC from Abc, use the EXACT function to make a case-sensitive comparison. EXACT returns TRUE only when the text in its two arguments matches, including capitalization.
With ABC in A2 and Abc in E2, the formula below returns FALSE: =EXACT(A2,E2). This is a direct comparison of two cells, not a lookup through a whole list. For a case-sensitive lookup across many rows, a common approach is to use EXACT to create a set of TRUE/FALSE comparisons and then look for TRUE. With the requested case-sensitive code in E2, codes in A2:A6, and results in B2:B6, use: =XLOOKUP(TRUE,EXACT(E2,A2:A6),B2:B6,"Not found",0). In Microsoft 365, this returns the result associated with the first case-sensitive match. For complex or older-version workbooks, a helper column with EXACT checks can be easier to audit.
Wildcards and literal asterisks
Another subtlety is that an exact-match setting and wildcard behavior are not identical across lookup functions. A wildcard is a character such as an asterisk, which can stand for multiple characters in some searches. VLOOKUP with FALSE and MATCH with 0 can interpret * and ? in a text lookup value as wildcards. Thus, an exact-match setting does not always mean those characters are treated literally.
XLOOKUP with match mode 0 treats wildcard characters as ordinary characters. If the value you need to find actually contains an asterisk or question mark, XLOOKUP can therefore be a convenient choice. XLOOKUP also has a separate wildcard match mode, but do not use that mode when you need a literal, whole-value comparison. Check how the chosen function handles special characters when searching codes that may contain them.
Fix common exact-match problems
If a formula returns #N/A even though the value appears in the list, check the underlying data before changing the match mode. A visible value can differ from its lookup counterpart because one has a trailing space, a nonprinting character, or a different data type. Also confirm that the lookup and return ranges cover the intended rows and that the lookup value is actually present.
- Check spaces: “North” and “North ” look similar on screen but are different text strings. TRIM can remove ordinary leading and trailing spaces, although it does not remove every kind of whitespace character.
- Check number versus text: The number 123 and the text value “123” may look alike but are stored differently. Make both sides consistent; changing number format alone changes appearance, not necessarily the stored value.
- Check hidden characters: Data copied from a website or another system can include nonbreaking spaces or control characters. Cleaning a helper column can reveal or remove some of these, but inspect the source rather than assuming every hidden character is the same.
- Check duplicates: An exact lookup usually returns the first match it finds. If duplicate keys have different results, decide which occurrence is correct before relying on the returned value.
For a helper column, suppose the original text is in A2 and you want to remove ordinary extra spaces, control characters, and nonbreaking spaces represented by character 160. This formula produces cleaned text in the helper cell: =TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," ")). Fill it down and use the cleaned helper values consistently in the lookup. CLEAN removes certain control characters, not every possible invisible Unicode character, so the source data may need additional inspection.
If duplicates are intentional and you need the last matching result rather than the first, XLOOKUP can search from the bottom upward. With the requested value in E2, keys in A2:A6, and results in B2:B6, this formula returns the result for the last matching key: =XLOOKUP(E2,A2:A6,B2:B6,"Not found",0,-1). The final -1 sets the search direction; it does not change the exact-match mode. This is useful for finding the most recent entry when rows are ordered chronologically, but only if that ordering reflects what “last” should mean.
Choose the right formula for the task
Use the function that matches the result you need and the Excel versions your audience has. In Microsoft 365, XLOOKUP is usually a direct option for returning a value. VLOOKUP is useful for compatibility with older files when the lookup key is at the left of the table. MATCH or XMATCH returns a position, while INDEX/MATCH uses that position to retrieve a related value.
- Return a value from a related column: Use XLOOKUP, or VLOOKUP with FALSE when the lookup key is the first table column.
- Return the position within a range: Use MATCH with 0, or XMATCH with 0 for an explicitly exact search.
- Support older Excel releases and look in either direction: Use INDEX with MATCH and specify 0 in MATCH.
- Require capitalization to match: Use EXACT comparisons, typically in a helper column or with XLOOKUP in a supported version.
- Handle duplicate keys: Decide whether the first or last occurrence is appropriate, and use a formula or data cleanup strategy that makes that rule clear.
For important workbooks, test both a value that should match and one that should not. Check a duplicate if duplicates are possible, and verify that the returned result comes from the intended row. This small review can catch a misplaced range or an accidental approximate lookup before the formula is copied through a report.
Frequently Asked Questions
What is the simplest exact-match formula in Excel?
For Microsoft 365 or Excel 2021 and later, XLOOKUP with match mode 0 is a clear option. Put the value to find in the first argument, then specify the lookup range and return range. You can add a not-found message to make missing values easier to recognize.
Does XLOOKUP use exact match by default?
Yes. XLOOKUP uses exact match by default, but adding 0 as the match-mode argument makes the requirement explicit. This is often helpful when someone else will maintain the workbook.
How do I make VLOOKUP exact?
Set VLOOKUP’s fourth argument to FALSE. For example, with the lookup value in E2 and the table in A2:B6, use =VLOOKUP(E2,A2:B6,2,FALSE). The lookup column must be the first column of that table range.
Why does exact match return #N/A?
The lookup value may differ from the listed value because of extra spaces, hidden characters, or a number-versus-text mismatch. Confirm that the item exists in the lookup range and that the ranges are correct before changing the formula.
Is exact match case-sensitive in Excel?
Usually not for XLOOKUP, VLOOKUP, MATCH, or XMATCH. Use EXACT when capitalization must be distinguished, such as when ABC and Abc represent different IDs.
Related
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUM Times Formula: Multiply Values, Then Add ThemIf quantities are in B2:B5 and unit prices are in C2:C5, use =SUMPRODUCT(B2:B5,C2:C5) to multiply each quantity by…
- Excel Goal Seeking: Find a Target Value with What-If ToolsExcel Goal Seek finds the input value that makes a formula return a result you specify. In Excel…
- Excel Formula for First Day of Month: ExamplesIf the date you want to use is in cell A2, enter =DATE(YEAR(A2),MONTH(A2),1) to return the first day…
- Excel Filter by Cell Colour: Steps, Limits and FixesTo filter by cell colour in Excel for Microsoft 365 on Windows, select a cell in your data,…
- Percentage Calculator in Excel: Formulas and ExamplesTo calculate a percentage in Excel, divide the part by the total, then format the result as a…
- Excel LEFT Function: Extract Text from the Left SideTo extract characters from the beginning of a cell in Excel, use the LEFT function. If A2 contains…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.