Case Sensitive Vlookup in Google Sheets [Solved]

0
286
Case Sensitive Vlookup in Google Sheets

As you know Vlookup function is case insensitive. That means it treats small case as well as upper case letters as same. It’s not possible to use functions like EXACT with VLOOKUP to make a case sensitive Vertical Lookup. Still Case Sensitive Vlookup in Google Sheets is possible. How? A Helper Column can solve this puzzle. If you are not interested in using Helper Column, then you can consider using a virtual helper column also.

How to Perform a Case Sensitive Vlookup in Google Sheets

See the sample data and then the lookup formula.

vlookup error when case sensitive

Here what I want to do is to find the Qty of Product IL102b which is in row # 6. But the above Vlookup formula wrongly return the result 300 from row # 5. Why? Vlookup treats IL102B as well as IL102b as same. Like most spreadsheet functions, Vlookup is case insensitive.

We can do a Case Sensitive Vlookup using a helper column as below.

Case Sensitive Vlookup in Google Sheets

Here Column A is a helper column. Now what you need to do is to use the following Regexextract Combo formula in Cell A2. Then copy and paste it to downwards up to Cell A5.

=ArrayFormula(concatenate(code(regexextract(B2, rept(“(.)”, len(B2))))))

The above same formula you can use. But rewrite the double quotes. Then apply the Vlookup formula as below.

Case sensitive vlookup with the help of regex

In this formula, Instead of D8, I’ve used D9 as lookup Search Key. You can either copy this code from the helper column itself or generate using the same formula in helper column as below.

Formula in D9.

=ArrayFormula(concatenate(code(regexextract(D8, rept(“(.)”, len(D8))))))

You can see that Case Sensitive Vlookup in Google Sheets is perfect this time. This Vlookup formula returns the qty 450 for the correct product.

If you are not satisfied with the helper column, you can generate a virtual helper column.

The formula would be look like as below.

case sensitive vlookup without helper column

You can follow my below tutorial to know how to create a virtual helper column in Google sheets. Enjoy!

How to Create a Virtual Helper Column in Google Sheets

LEAVE A REPLY

Please enter your comment!
Please enter your name here