The VLOOKUP function is case-insensitive. However, you can achieve a case-sensitive VLOOKUP in Google Sheets. How?

You can use additional functions in combination with VLOOKUP to achieve this, including CODE, EXACT, and REGEXMATCH. You have the option to use one of these functions to perform case-sensitive matches.

In fact, you don’t even need to use VLOOKUP for case-sensitive lookups. You can utilize other functions like XLOOKUP, FILTER, or INDEX-MATCH.

You can learn how to use these functions with easy-to-follow examples provided below.

Sample Data and Case-Insensitive VLOOKUP Formula

In column A, I have product IDs, column B contains corresponding descriptions, and column C displays the available quantities for each product. We intend to perform a vertical lookup in this table.

You may notice that some products share the same IDs, and to differentiate them based on their make, I’ve used an uppercase letter “A” and a lowercase letter “a.”

For example, “SH_101A” and “SH_101a” represent the same product, which is a safety helmet, but they have different makes.

I need to search for a product and return its product name. Here is the standard formula when the criterion (search_key) is specified in cell E2:

=VLOOKUP(E2, A2:C, 2, 0) // returns description

Syntax:

VLOOKUP(search_key, range, index, [is_sorted])

However, the result is not accurate because the formula is case-insensitive.

First, let’s learn how to use a case-sensitive VLOOKUP in Google Sheets. After that, we’ll explore popular alternatives.

Case Sensitive VLOOKUP Formula Examples for Google Sheets

We will employ three functions to introduce case sensitivity to the VLOOKUP: EXACT, REGEXMATCH, and CODE.

Let’s examine each of them individually.

Using the EXACT Function with VLOOKUP

We typically use the EXACT function to check if two strings are identical. However, it can also be used to compare a string to a list of strings. In the latter case, we need to use the ArrayFormula function in conjunction with EXACT.

For instance, the following formula will return an array of TRUE or FALSE values, indicating where a case-sensitive match is found:

=ArrayFormula(EXACT(E2, A2:A))

Here, we use EXACT to identify the case-sensitive match of the search_key in the first column of the range.

In the VLOOKUP range A2:C, we need to replace A2:A with this result. How can we achieve that?

We will accomplish this by creating a virtual range for VLOOKUP.

To create a virtual range for VLOOKUP, we should utilize Curly Braces or HSTACK. In our case-sensitive VLOOKUP formula, the range should be defined as {exact_result, B2:C}.

Here is the case-sensitive VLOOKUP formula:

=ArrayFormula(VLOOKUP(TRUE, {EXACT(E2, A2:A), B2:C}, 2, 0))

In this case, the search_key is TRUE, not the one in E2, because the first column in the range now contains only TRUE or FALSE values.

I recommend using this method for case-sensitive VLOOKUP in Google Sheets.

Note: If your search_key is a number or date, it should be formatted in a similar manner to the first column (lookup array) in the range.

Using the REGEXMATCH Function with VLOOKUP

This is similar to the previous example. Here, we’ll replace the EXACT function with the REGEXMATCH function.

Replace EXACT(E2, A2:A) with REGEXMATCH(A2:A, "^" & E2 & "$").

Formula:

=ArrayFormula(VLOOKUP(TRUE, {REGEXMATCH(A2:A, "^"&E2&"$"), B2:C}, 2, 0))

REGEXMATCH uses regular expressions for pattern matching, and it will also return TRUE or FALSE values. It has a slight advantage over the previous formula. What’s that?

You can convert the REGEXMATCH-based case-sensitive VLOOKUP to be case-insensitive by simply modifying the regular expression from "^" & E2 & "$" to "(?i)^" & E2 & "$".

Furthermore, you can adapt it to perform a partial match of the search key by changing the expression from "^" & E2 & "$" to just E2.

Using the CODE Function with VLOOKUP

I don’t prefer this method, but I’m including it here to help you understand VLOOKUP formulas created by others.

In this case-sensitive VLOOKUP approach, we use the Unicode map value of the search_key to find a match in the first column of the range, which also contains Unicode map values.

The reason I don’t prefer this method is that the CODE function only returns the Unicode map value of the first character in the string. Therefore, we need to extract each character, obtain the code, and concatenate them. This method also requires the use of a helper column, making it less flexible.

Here’s how it works:

Insert the following formula in cell D2 and drag it down as far as needed, such as up to D5:

=ArrayFormula(JOIN("",CODE(MID(A2, SEQUENCE(LEN(A2)), 1)))

Note: In a previous tutorial, I explained how to extract each character from a string. You can refer to that tutorial: Formula to Reverse Text and Numbers in Google Sheets.

The result of the above formula becomes the first column in the range, so the range will be {D2:D, B2:C}. We need to convert the search key in a similar way using the formula mentioned above.

The VLOOKUP formula will then look like this:

=VLOOKUP(ArrayFormula(JOIN("",CODE(MID(E2, SEQUENCE(LEN(E2)), 1))), {D2:D, B2:C}, 2, 0)

Where:

• Search_key: ArrayFormula(JOIN("",CODE(MID(E2, SEQUENCE(LEN(E2)), 1)))
• range: {D2:D, B2:C}
• index: 2
• is_sorted: 0

Case Sensitive VLOOKUP Alternatives for Google Sheets

I mentioned using several alternatives to achieve case-sensitive VLOOKUP in Google Sheets. These alternatives all require the use of supporting functions, and my preference is for EXACT.

Formula #1: XLOOKUP

=ArrayFormula(XLOOKUP(TRUE, EXACT(E2, A2:A), B2:B))

Syntax of the XLOOKUP Function:

XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

The XLOOKUP formula searches for TRUE in the lookup_range, which corresponds to the EXACT result, and returns the value from the result_range of the matching row.

Formula #2: FILTER

=FILTER(B2:B, EXACT(E2, A2:A)=TRUE)

Syntax:

FILTER(range, condition1, [condition2, …])

This FILTER formula filters the description when the EXACT result is equal to TRUE.

Formula #3: INDEX-MATCH

=INDEX(B2:B, MATCH(TRUE, EXACT(E2, A2:A), 0))

The INDEX-MATCH method remains a popular choice for many Excel users when transitioning from case-sensitive VLOOKUP. This method also works in Google Sheets.

The MATCH formula searches for TRUE in the EXACT results and returns the position. This position is then used as the row offset in the INDEX formula within column B.