Are you tired of the limitations of the VLOOKUP function in Google Sheets? Specifically, its case-insensitivity? Well, you’re in luck! There are ways to achieve a case-sensitive VLOOKUP in Google Sheets that you may not be aware of. In this article, we’ll explore some functions you can use in combination with VLOOKUP to achieve this. We’ll also discuss alternative methods that can provide the same results. So, let’s dive right in!
Sample Data and Case-Insensitive VLOOKUP Formula
Let’s start by looking at an example. Suppose you have a table with product IDs, descriptions, and quantities. You want to perform a vertical lookup in this table. However, the standard VLOOKUP formula is case-insensitive, which means it won’t give you accurate results when you’re looking for a case-sensitive match.
As you can see in the image above, some products share the same IDs but have different makes. For example, “SH_101A” and “SH_101a” represent the same product, a safety helmet, but they have different makes.
The standard VLOOKUP formula when the criterion (search_key) is specified in cell E2 would be:
=VLOOKUP(E2, A2:C, 2, 0) //returns descriptionUnfortunately, this formula is case-insensitive and won’t differentiate between “SH_101A” and “SH_101a”.
Case Sensitive VLOOKUP Formula Examples for Google Sheets
Now, let’s explore three functions that can introduce case sensitivity to the VLOOKUP formula: EXACT, REGEXMATCH, and CODE.
Using the EXACT Function with VLOOKUP
The EXACT function is typically used to check if two strings are identical. However, it can also be used to compare a string to a list of strings. In this case, we need to use the ArrayFormula function in conjunction with EXACT.
To create a case-sensitive VLOOKUP formula using the EXACT function, follow these steps:
- Use the following formula to check for a case-sensitive match in the first column of your range:
=ArrayFormula(EXACT(E2, A2:A))- Use Curly Braces or HSTACK to create a virtual range for VLOOKUP. In our case, the range should be defined as
{exact_result, B2:C}.
With these steps, your case-sensitive VLOOKUP formula would be:
=ArrayFormula(VLOOKUP(TRUE, {EXACT(E2, A2:A), B2:C}, 2, 0))Using the REGEXMATCH Function with VLOOKUP
The REGEXMATCH function can also be used to achieve a case-sensitive VLOOKUP in Google Sheets. Follow these steps:
- Replace
EXACT(E2, A2:A)withREGEXMATCH(A2:A, "^" & E2 & "$").
Your case-sensitive VLOOKUP formula using the REGEXMATCH function would be:
=ArrayFormula(VLOOKUP(TRUE, {REGEXMATCH(A2:A, "^" & E2 & "$"), B2:C}, 2, 0))Using the CODE Function with VLOOKUP
Although not my preferred method, I’ll also explain how to use the CODE function with VLOOKUP for case-sensitive lookups. This method involves finding matches based on the Unicode map values of the search key and the first column in the range.
Here are the steps to create a case-sensitive VLOOKUP formula using the CODE function:
- Use the following formula to extract each character’s Unicode map value in the search key:
=ArrayFormula(JOIN("",CODE(MID(E2, SEQUENCE(LEN(E2)), 1))))-
Use the same formula to obtain the Unicode map values in the first column of your range.
-
Use the obtained ranges in the VLOOKUP formula:
=VLOOKUP(ArrayFormula(JOIN("",CODE(MID(E2, SEQUENCE(LEN(E2)), 1)))), {D2:D, B2:C}, 2, 0)Case Sensitive VLOOKUP Alternatives for Google Sheets
Apart from using supporting functions with VLOOKUP, you also have alternative methods to achieve case-sensitive lookups in Google Sheets. These alternatives involve the use of other functions like XLOOKUP, FILTER, and INDEX-MATCH.
Formula #1: XLOOKUP
The XLOOKUP function is a powerful alternative to VLOOKUP and can also be used for case-sensitive lookups. Here’s the formula you can use:
=ArrayFormula(XLOOKUP(TRUE, EXACT(E2, A2:A), B2:B))Formula #2: FILTER
The FILTER function can help you achieve case-sensitive lookups by filtering the description based on the EXACT result. Here’s the formula:
=FILTER(B2:B, EXACT(E2, A2:A)=TRUE)Formula #3: INDEX-MATCH
The INDEX-MATCH method remains a popular choice for many Excel users and can also be used in Google Sheets. Here’s the formula:
=INDEX(B2:B, MATCH(TRUE, EXACT(E2, A2:A), 0))These alternative methods provide the same case-sensitive results as the supporting function approach. However, my personal preference is to use the EXACT function with VLOOKUP.
That’s it! You now have the knowledge and tools to perform case-sensitive lookups in Google Sheets using VLOOKUP and other supporting functions. Experiment with different approaches and find the one that suits your needs best. Happy data analysis!
Find more helpful articles like this at Crawlan.com.
