XLOOKUP Case Sensitive? Yes, Here’s How

Yes, XLOOKUP can perform case-sensitive lookups, but it requires a small addition to the formula. By default, XLOOKUP ignores case, treating “apple” and “APPLE” as the same. To make it case-sensitive, you need to use the EXACT function inside the lookup_array argument, which forces the match to consider letter case.

Background: Why Case Sensitivity Matters

In Excel, most lookup functions like VLOOKUP, HLOOKUP, and INDEX/MATCH are case-insensitive by default. This is usually fine for data like names or product codes where case differences are accidental. However, some datasets treat case as meaningful: product SKUs where “abc123” and “ABC123” are different items, user IDs, file names, or codes that follow a case-sensitive convention. In those situations, a case-insensitive lookup can return the wrong record silently, which can be a serious problem for reporting or data analysis.

XLOOKUP, introduced in Excel 2021 and Microsoft 365, follows the same default behavior as its predecessors: it ignores case. But unlike VLOOKUP, XLOOKUP offers a clean and direct way to make the match case-sensitive without needing array formulas or helper columns.

How XLOOKUP Works by Default

XLOOKUP’s syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The match_mode argument controls whether the lookup is exact or approximate. A value of 0 (the default) means exact match, but “exact” here refers to the value itself, not the case. So =XLOOKUP("apple", A2:A10, B2:B10) will find the first cell in A2:A10 that equals “apple” regardless of whether it is “Apple”, “APPLE”, or “apple”.

If your data has both “apple” and “APPLE” in the lookup array, XLOOKUP returns the first one it encounters, which may not be the one you intended. That is why case-sensitive matching is sometimes essential.

How to Make XLOOKUP Case-Sensitive

To force XLOOKUP to respect case, you wrap the lookup_array with the EXACT function. EXACT compares two text strings and returns TRUE only if they are identical in characters and case. The formula becomes:

=XLOOKUP(TRUE, EXACT(lookup_value, lookup_array), return_array)

Here is how it works step by step:

1. EXACT(lookup_value, lookup_array) returns an array of TRUE/FALSE values. TRUE appears only where the cell matches the lookup value exactly, including case.
2. XLOOKUP(TRUE, …) then searches for the first TRUE in that array, effectively finding the first case-sensitive match.
3. The return_array is the same as in a normal XLOOKUP, so you get the corresponding value from that column.

For example, if A2:A10 contains “apple”, “APPLE”, “Apple”, and you want the value next to “APPLE”, use:

=XLOOKUP(TRUE, EXACT("APPLE", A2:A10), B2:B10)

This returns the B value from the row where A is exactly “APPLE”, ignoring “apple” and “Apple”.

Choosing Between Case-Sensitive and Case-Insensitive

When should you use each? Here are some practical guidelines:

• Use case-insensitive (default) when case is not meaningful: names, city names, general text entries, or data entered by humans without a strict convention.
• Use case-sensitive when the data is machine-generated, follows a strict code system, or when you have verified that two entries differ only by case and represent different items.
• When in doubt, check your data for duplicates that differ only by case. If none exist, the default is fine. If they exist, you need the case-sensitive version.

Be aware that making a lookup case-sensitive can cause #N/A errors if the case does not match exactly. For example, if your data has “Product” and you search for “product”, the case-sensitive formula will not find it. This is often desirable, but it can surprise users who are used to case-insensitive behavior.

Common Problems and How to Fix Them

Here are the most frequent issues people run into with case-sensitive XLOOKUP:

• #N/A error: This usually means no exact case match exists. Double-check the case of your lookup value and the data. Use the TRIM function to remove extra spaces, as EXACT treats spaces as significant.
• Returning the wrong value: If your data has multiple entries with the same case (e.g., two “APPLE” rows), XLOOKUP returns the first one. To get the last match, you can use the search_mode argument with a value of -1 (search from last to first).
• Array behavior: The EXACT function returns an array, so if you are using an older Excel version that does not support dynamic arrays, you may need to enter the formula with Ctrl+Shift+Enter. In Excel 365 and 2021, it works normally.
• Performance: Using EXACT over a large range (tens of thousands of rows) can be slower than a plain XLOOKUP because it evaluates the entire array. If performance is an issue, consider sorting your data and using a binary search, but that is rarely necessary for typical spreadsheet sizes.

Comparison with Other Case-Sensitive Lookup Methods

Before XLOOKUP, users had to use INDEX/MATCH with EXACT, or array formulas. The old formula looked like:

=INDEX(return_range, MATCH(TRUE, EXACT(lookup_value, lookup_range), 0))

This works but requires entering as an array formula in older Excel. XLOOKUP simplifies this into a single, non-array formula. VLOOKUP cannot be made case-sensitive without adding a helper column that uses EXACT, which is clunky. So XLOOKUP is the cleanest method available in modern Excel.

Another alternative is to use the FILTER function: =FILTER(return_array, EXACT(lookup_value, lookup_array)). This returns all matches, not just the first, which can be useful if you need multiple results. However, if you only need one value, XLOOKUP is more straightforward.

Frequently Asked Questions

Does XLOOKUP work case-sensitively in Excel 365? Yes, the technique works in Excel 365, Excel 2021, and later versions. The EXACT function is available in all modern Excel versions, so the formula is compatible.

Can I use wildcards with case-sensitive XLOOKUP? No. When you use EXACT, wildcards like * and ? are treated as literal characters. If you need case-sensitive matching with wildcards, you would need a more complex approach, such as using FILTER with a combination of ISNUMBER and SEARCH (but SEARCH is case-insensitive). There is no built-in case-sensitive wildcard lookup in Excel.

Why does my case-sensitive XLOOKUP return #N/A even though I see the value? This usually happens because of hidden characters like spaces or non-breaking spaces. Use the LEN function to compare the lengths of the lookup value and the cell. Also, check if the cell has a line break or other non-printing character.

Is there a way to make XLOOKUP case-insensitive but still distinguish between two values that differ only by case? No, if you need to distinguish, you must use case-sensitive matching. The default is case-insensitive, so you must explicitly use EXACT to get case sensitivity.

Does the case-sensitive XLOOKUP work with numbers? Yes, but numbers do not have case. The EXACT function works with numbers as well, but it compares them as text. If your lookup value is a number stored as text, it will work. If it is a real number, it will match regardless of case, since numbers have no case.

Can I use this technique in Google Sheets? Yes, the same formula works in Google Sheets. XLOOKUP is available in Google Sheets, and the EXACT function also exists, so you can use the same approach.

Related

• Key Kubernetes Trends That Defined Modern Cloud Native
• Loom Competitors and Alternatives Compared
• Research Idea Theft: What Counts as Stealing an Idea?