ExcelInsider Team
The standard VLOOKUP formula, by default, is not case-sensitive. That means it treats uppercase and lowercase characters as the same. For example, it will consider “apple,” “Apple,” and “APPLE” as the same value. This hampers data accuracy and ignores the case differences. However, case-sensitivity becomes essential when handling information such as usernames, passwords, and even product codes or SKUs. The article explains how to enable case sensitivity in the VLOOKUP function and other functions that can be modified to be case-sensitive.
➤ Enter the formula: =VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(lookup_value, lookup_range), return_range), 2, 0)
➤ Replace the “lookup_value” that you want to look up (case-sensitive).
➤ Input “lookup_range” which means the range of cells where the lookup will happen.
➤ Insert “return_range”, the range of cells containing the values to return.
➤ Press the Enter button and drag the formula to the cells below using the Fill Handle tool.
This article discusses the ways of how to vlookup with case differences using the VLOOKUP, XLOOKUP, SUMPRODUCT, INDEX-MATCH formulas.
Why Is Case-Sensitive Vlookup Important in Excel?
Case sensitivity is essential in situations where capitalization distinguishes different categories, codes, or identifiers, such as SKUs (Stock Keeping Units), usernames, product codes, or passwords. Ignoring case differences can lead to incorrect data retrieval, merging distinct items into one mistaken identity. Serious misinterpretations or bad business choices can result from inaccurate data.
In the following dataset, we have used the “=VLOOKUP(E2,A2:C13,3,FALSE)” formula using the VLOOKUP function only in the F2 cell to get the price of “abc123” product. But the formula finds the price of “ABC123” product. That means the VLOOKUP function ignores case differences.
Therefore, we must make the VLOOKUP function case sensitive to ensure data accuracy and account for differences in capitalization.
Making VLOOKUP Case-Sensitive by Combining It with Other Functions
To make the VLOOKUP function case-sensitive, combine it with the EXACT and CHOOSE functions to create a virtual table that considers case differences. You can find the price for the product code “abc123,” which is located in cell E2, by following the steps below.
Steps:
➤ Go to the F2 cell and enter the following formula.
=VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(E2,A2:A13), C2:C13), 2, 0)
➤ Press the Enter button and drag the formula to the cells below using the Fill Handle tool.
XLOOKUP Function to Vlookup Considering Case Differences
The XLOOKUP function (available in Microsoft 365 and Excel 2021) offers a modern alternative to VLOOKUP and can be made case-sensitive with the EXACT function. We have used the following formula in the F2 cell.
=XLOOKUP(TRUE, EXACT(E2, A2:A13), C2:C13, "Not found")
Here, the EXACT(E2, A2:A13) returns an array of TRUE/FALSE for case-sensitive matches.
And the XLOOKUP(TRUE, …, C2:C13, “Not found”) returns the price where TRUE is found, or “Not found” if no match exists.
Using SUMPRODUCT Function to Case-Sensitive Lookups
The SUMPRODUCT function can be used for case-sensitive lookups, particularly when retrieving numeric values like prices. Here, you can use the following formula in the F2 cell.
=SUMPRODUCT((EXACT(A2:A13,E2) * (C2:C13)))
In this formula, the EXACT(A2:A13,E2)*(C2:C13) converts TRUE to 1 and multiplies by the price, effectively selecting the matching price. Finally, the SUMPRODUCT sums up values from C2:C13 only where the corresponding value in A2:A13 exactly matches the E2 cell.
Note:
This method is applicable only for getting numerical values. For text, use VLOOKUP, XLOOKUP, or INDEX-MATCH.
INDEX-MATCH Formula to Make Vlookup Case Sensitive
Lastly, you can also use the INDEX-MATCH formula to vlookup with case sensitivity. Use the following formula.
=INDEX(C2:C13, MATCH(TRUE, EXACT(A2:A13, E2), 0))
Here, the EXACT(A2:A13, E2) function checks the value with case differences in the A2:A13 cells against the value of the E2 cell. Then the MATCH function returns the row number where TRUE matches, which is 2. Finally, the retrieves the corresponding price from the 2nd row, which is $499.
Frequently Asked Questions
Are there limitations to making VLOOKUP case sensitive?
If multiple matching values differ only by case, the formula will return the first exact match found. Another thing is that some solutions require entering array formulas using CTRL+SHIFT+ENTER as some functions (e.g. XLOOKUP) are available only Microsoft 365 and Excel 2021 or later.
Is there a limit to what case-sensitive lookup formulas can compare?
Case-sensitive formulas can only return the first match where the case exactly matches. If multiple matches exist differing only in case, only the first is returned.
Can I automate case-sensitive VLOOKUP with VBA?
Yes, a custom VBA function can perform case-sensitive lookups. You can use StrComp with vbBinaryCompare to enforce case sensitivity. Apply it as =CaseSensitiveVLOOKUP(E2, A2:C13, 3, FALSE) to find the price for Xyz789 ($199). This requires enabling macros in Excel.
Wrapping Up
This is how you make the vlookup search case-sensitive to get accurate data. By combining EXACT with VLOOKUP, XLOOKUP, SUMPRODUCT, or INDEX-MATCH, we can make to retrieve the proper data. We hope that you have enjoyed the article. Feel free to download the practice workbook and share your thoughts in the comments!
