No. In Excel for Microsoft 365, XLOOKUP is not case-sensitive: it treats text such as “Maple,” “MAPLE,” and “maple” as the same lookup value. To require an exact match of uppercase and lowercase letters, combine XLOOKUP with the case-sensitive EXACT function.
What XLOOKUP’s default match actually means
XLOOKUP uses exact matching by default, but “exact” in this setting does not mean “case-sensitive.” It means Excel looks for the specified value rather than an approximate match. For text, the comparison ignores differences in letter case, so a lookup for “MAPLE” can match “Maple.”
The function’s general structure is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The optional match_mode controls whether the lookup is exact, approximate, or uses wildcard characters. Omitting it, or setting it to 0, requests an exact match—but that exact text match is still case-insensitive.
Case and match mode are separate ideas. Changing match_mode to 0 will not make the lookup case-sensitive, and choosing wildcard matching will not add case sensitivity either. If capitalization matters, the comparison itself must use a function that distinguishes uppercase from lowercase.
Make XLOOKUP case-sensitive with EXACT
EXACT compares two text strings and returns TRUE only when their characters match, including their capitalization. You can give the array of TRUE and FALSE results to XLOOKUP, then ask XLOOKUP to find TRUE. This preserves XLOOKUP’s convenient return and not-found behavior while making the text comparison case-sensitive.
Assume the lookup names are in A2:A6, the corresponding IDs are in B2:B6, and the value to search for is in E2. Use:
=XLOOKUP(TRUE,EXACT(E2,A2:A6),B2:B6,"Not found",0)
If A2:A6 contains “Maple,” “MAPLE,” “maple,” “Oak,” and “Pine,” and the corresponding values in B2:B6 are 101, 102, 103, 104, and 105, entering “MAPLE” in E2 returns 102. Entering “Maple” returns 101, while “maple” returns 103. If E2 is “MaPle,” the formula returns “Not found” because none of the entries has that exact capitalization.
In current Microsoft 365 Excel, EXACT evaluates each item in A2:A6, and XLOOKUP searches the resulting TRUE/FALSE array. The final 0 explicitly selects exact matching for TRUE. You do not need to enter this as a legacy array formula with Ctrl+Shift+Enter; press Enter normally. The formula also works in Excel 2021, which supports XLOOKUP and dynamic arrays.
Walk through a case-sensitive lookup example
To build the formula, first identify the text column to search, the column containing the value to return, and the cell holding the requested text. Keep the lookup and return ranges aligned: if the lookup range has five rows, the return range should refer to those same five records.
- Put the entries to search in A2:A6 and their corresponding IDs in B2:B6.
- Enter the requested spelling, with its intended capitalization, in E2.
- Enter the EXACT-and-XLOOKUP formula in another cell, such as F2.
- Change E2 to another capitalization and observe that the returned ID changes—or that the formula reports no match.
This arrangement is useful for product codes, usernames, or other identifiers only when the system supplying the data treats capitalization as meaningful. For ordinary names, a case-sensitive lookup may be unnecessarily strict: “Lee” and “LEE” might be intended to identify the same person. Confirm the data rules before choosing the stricter comparison.
The formula returns the value from the first row whose text matches the requested capitalization. If the same exact spelling appears more than once, XLOOKUP’s default search direction is from the first item to the last, so it returns the first corresponding result. You can change that behavior with the search_mode argument, but doing so does not change whether the comparison distinguishes case.
Not-found results, duplicates, and text details
The fourth XLOOKUP argument in the example, “Not found,” is an optional message displayed when no case-sensitive match exists. You can replace it with another message, or omit that argument if you prefer Excel’s standard #N/A error. A deliberate message can make a report easier to read, while #N/A can be useful when a missing value should be noticed or handled by another formula.
Because EXACT compares characters, extra spaces can also prevent a match. For example, “MAPLE” and “MAPLE ” are different strings. If the source data may contain accidental spaces, inspect or clean the entries before deciding how to handle them. TRIM can remove ordinary extra spaces in many situations, but applying text cleanup can change the data being compared; it should not be used if spaces are meaningful identifiers.
Case-insensitive XLOOKUP can also produce an unexpected result when entries differ only by capitalization. If “Maple” and “MAPLE” both exist, a standard lookup for either form treats both as eligible and returns whichever qualifying row comes first in the chosen search direction. Sorting the list can therefore change which result a case-insensitive formula returns. The EXACT approach avoids that ambiguity when each capitalization is intended to identify a distinct record.
Text-versus-number mismatches are a separate issue from capitalization. A value stored as the number 123 and a text string that looks like “123” may not behave as the same data in every formula setup. If a lookup seems to fail, check whether the lookup value and source values use consistent data types as well as consistent spelling.
How to choose between ordinary and case-sensitive matching
Use the standard XLOOKUP when capitalization is merely a formatting choice. It is simpler, easier for another spreadsheet user to recognize, and appropriate for most name, category, and description lookups. A lookup for “north” can find “North” without needing a special formula.
Use EXACT with XLOOKUP when upper- and lowercase forms must be treated as separate keys. This can matter for systems that issue case-sensitive identifiers or when the data explicitly contains distinct values that differ only in capitalization. Before using it, consider whether people entering or copying the lookup value can reliably preserve the required case.
- Choose normal XLOOKUP when case differences should not affect the result.
- Choose EXACT with XLOOKUP when a capitalization difference must count as a different value.
- Choose a helper column when you want to make the comparison results visible and easier to troubleshoot.
- Check data quality first if unexpected spaces, mixed data types, or duplicate keys could also explain the result.
Use a helper column to see which rows match
A helper column makes the comparison transparent. With the lookup value in E2 and the text to search in A2:A6, enter this in C2 and copy it down through C6:
=EXACT($E$2,A2)
For the example where E2 contains “MAPLE,” the helper results beside “Maple,” “MAPLE,” and “maple” are FALSE, TRUE, and FALSE. That visible TRUE identifies the row that has the exact capitalization. If every result is FALSE, inspect the lookup text for spelling, spaces, and capitalization before changing the lookup formula.
If the IDs remain in B2:B6 and the TRUE/FALSE helper results are in C2:C6, use this formula to return the ID for the matching row:
=INDEX(B2:B6,MATCH(TRUE,C2:C6,0))
With the same example data and “MAPLE” in E2, this returns 102. MATCH finds the position of the first TRUE in C2:C6, and INDEX returns the value at the corresponding position in B2:B6. This approach is helpful when you need to inspect or reuse the case-sensitive match flags. If no row is TRUE, the formula returns #N/A unless you choose to handle that result separately.
Compatibility and workbook considerations
XLOOKUP is available in Microsoft 365 and Excel 2021, but it is not available in Excel 2019 or Excel 2016. In older versions, the helper-column method above can still use INDEX and MATCH, which are available in those versions. The EXACT function is also a longstanding Excel function.
The formulas shown work in Microsoft 365 Excel for Windows. The same functions and formula approach are available in Microsoft 365 Excel for Mac and Excel for the web, although menus and some interface details differ between platforms. If a workbook is opened in an older Excel version that does not support XLOOKUP, the XLOOKUP formula may show a compatibility error; use the helper-column INDEX/MATCH approach instead.
For a large dataset, avoid searching entire columns with an array comparison unless you have a specific reason to do so. EXACT needs to compare the lookup value with each cell in its range, so bounded ranges or an Excel Table’s data columns are generally easier to manage. Keep the lookup and return areas matched in size, and make sure copied formulas refer to the intended lookup cell and data range.
Frequently Asked Questions
Does XLOOKUP match uppercase and lowercase differently?
No. Standard XLOOKUP ignores case when matching text. “ALPHA” can match “Alpha.” Use EXACT with XLOOKUP if capitalization must be part of the match.
Does setting match_mode to 0 make XLOOKUP case-sensitive?
No. A match_mode of 0 requests an exact match rather than an approximate or wildcard match, but the text comparison remains case-insensitive. EXACT is needed for case-sensitive text matching.
Can XLOOKUP return different results for “abc” and “ABC”?
Yes, if you use the EXACT-based formula and the lookup range contains those spellings as separate entries. With ordinary XLOOKUP, those lookup values are treated as equivalent text matches.
Why does my case-sensitive XLOOKUP return “Not found”?
Check the capitalization, spelling, and spaces in both the lookup value and source entry. Also confirm that the ranges are aligned and that the searched values are text in a consistent format. EXACT requires the compared characters to match.
Can I make XLOOKUP case-sensitive without EXACT?
Not by changing XLOOKUP’s match_mode or search_mode. Those arguments control match type and search direction, not case sensitivity. EXACT is the straightforward way to supply case-sensitive comparisons.
Does this formula need Ctrl+Shift+Enter?
No. In current Microsoft 365 Excel and Excel 2021, enter the EXACT-and-XLOOKUP formula with Enter. It evaluates the comparison array as part of the formula.
Related
- Remove a Data Model from Excel: Steps and Effects
- Excel Data Modeling: How It Works and When to Use It
- Excel Data Modelling: Tables, Relationships and Pivots
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel UPPER Function: How to Make Text All CapsExcel’s uppercase text function is UPPER. In Microsoft Excel for Microsoft 365, enter =UPPER(A2) to convert the text…
- XLOOKUP Examples With Arrays in ExcelWith product names in A2:A6, prices in B2:B6, and a name to find in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"Not found").…
- Excel Year Quarter Formula: Get Year and Quarter from DatesWith a valid Excel date in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from…
- Excel Formula for Counting Unique ValuesIn Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) to count distinct nonblank values in A2:A100. It returns 0 if…
- AVERAGEIF Excel Range: Syntax, Examples, and Common FixesIn Excel, the range in AVERAGEIF is the group of cells Excel checks against your condition; the optional…
- Excel Sum Every Other Column: Formulas and ExamplesTo sum every other column in an Excel row, use =SUMPRODUCT((MOD(COLUMN(B2:K2)-COLUMN(B2),2)=0)*B2:K2). With numbers in B2:K2, this adds B2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.