How to Perform a Case-Sensitive Lookup in Excel Formula

In Excel, you may often need to look up a value from a table based on a given criterion. For example, you may want to find the price of a product based on its name, or the age of a person based on their full name. However, what if the criterion is case-sensitive, meaning that uppercase and lowercase letters matter? For instance, what if you have two products with the same name but different cases, such as “apple” and “Apple”, and you want to find the price of the exact match?

By default, Excel is not case-sensitive. This means that standard lookup functions like VLOOKUP, XLOOKUP, and INDEX and MATCH are also not case-sensitive. These formulas will simply return the first match, ignoring upper and lower case. Therefore, you need a way to make Excel lookup functions case-sensitive, so that they can distinguish between “apple” and “Apple”, or “Jill Smith” and “JILL SMITH”.

In this article, we will show you how to perform a case-sensitive lookup in Excel formula using different methods. We will explain the basic theory behind each method, the procedures to follow, and a detailed example with real data. We will also compare the advantages and disadvantages of each method, and suggest some alternative approaches.

Method 1: Using EXACT and CHOOSE with VLOOKUP

One way to perform a case-sensitive lookup in Excel is to use the EXACT function with the CHOOSE function and the VLOOKUP function. The EXACT function compares two text strings in a case-sensitive fashion, and returns TRUE if they are exactly the same, or FALSE otherwise. The CHOOSE function returns a value from a list of values based on a given index number. The VLOOKUP function looks up a value in the first column of a table, and returns a corresponding value from another column.

The basic formula for this method is:

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(lookup_value,lookup_array),return_array),2,0)

where:

• lookup_value is the value you want to look up in a case-sensitive manner.
• lookup_array is the range of cells that contains the lookup values.
• return_array is the range of cells that contains the return values.
• 2 is the column index number that specifies the column from which to return a value.
• 0 is the match type that specifies an exact match.

The logic behind this formula is:

• The EXACT function compares the lookup_value with each value in the lookup_array, and returns an array of TRUE and FALSE values, depending on whether they match exactly or not.
• The CHOOSE function creates a two-column array, where the first column is the array of TRUE and FALSE values returned by the EXACT function, and the second column is the return_array.
• The VLOOKUP function looks up the value TRUE in the first column of the array created by the CHOOSE function, and returns the corresponding value from the second column.

Example

Suppose you have a table of products with their names and prices, as shown below:

You want to find the price of the product “Apple” with an uppercase “A”. If you use a standard VLOOKUP formula, such as:

=VLOOKUP("Apple",A2:B7,2,0)

You will get the wrong result of $1.00, because Excel will return the first match it finds, which is “apple” with a lowercase “a”.

To perform a case-sensitive lookup, you can use the formula:

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT("Apple",A2:A7),B2:B7),2,0)

This formula will return the correct result of $1.50, because it will compare “Apple” with each value in the range A2:A7 in a case-sensitive manner, and return the corresponding value from the range B2:B7.

Advantages and Disadvantages

The advantages of this method are:

• It is simple and elegant, and does not require any helper columns or array formulas.
• It works in all versions of Excel, including Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.

The disadvantages of this method are:

• It can only look up from left to right, meaning that the lookup value must be in the first column of the table, and the return value must be in a column to the right of the lookup value.
• It can only return one value per lookup, meaning that if there are multiple matches with the same case, it will return the first match it finds.

Method 2: Using EXACT and XLOOKUP

Another way to perform a case-sensitive lookup in Excel is to use the EXACT function with the XLOOKUP function. The XLOOKUP function is a new function introduced in Excel 365, which can look up a value in any column of a table, and return a corresponding value from any other column. It can also handle different types of matches, such as exact match, approximate match, wildcard match, and binary search.

The basic formula for this method is:

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

where:

• lookup_value is the value you want to look up in a case-sensitive manner.
• lookup_array is the range of cells that contains the lookup values.
• return_array is the range of cells that contains the return values.

The logic behind this formula is:

• The EXACT function compares the lookup_value with each value in the lookup_array, and returns an array of TRUE and FALSE values, depending on whether they match exactly or not.
• The XLOOKUP function looks up the value TRUE in the array returned by the EXACT function, and returns the corresponding value from the return_array.

Example

Using the same table of products as before, you want to find the price of the product “Apple” with an uppercase “A”. If you use the XLOOKUP formula, such as:

=XLOOKUP(TRUE,EXACT("Apple",A2:A7),B2:B7)

You will get the correct result of $1.50, because it will compare “Apple” with each value in the range A2:A7 in a case-sensitive manner, and return the corresponding value from the range B2:B7.

Advantages and Disadvantages

The advantages of this method are:

• It is more flexible and powerful than the VLOOKUP method, as it can look up from any column to any column, and handle different types of matches.
• It can return multiple values per lookup, meaning that if there are multiple matches with the same case, it will return all of them in a spill range.

The disadvantages of this method are:

• It only works in Excel 365, and is not compatible with older versions of Excel.
• It may require more calculation time and memory than the VLOOKUP method, especially for large data sets.

Method 3: Using EXACT and INDEX MATCH

A third way to perform a case-sensitive lookup in Excel is to use the EXACT function with the INDEX and MATCH functions. The INDEX function returns a value from a range of cells based on a given row and column number. The MATCH function returns the relative position of a value in a range of cells based on a given match type.

The basic formula for this method is:

=INDEX(return_array,MATCH(TRUE,EXACT(lookup_value,lookup_array),0))

where:

• lookup_value is the value you want to look up in a case-sensitive manner.
• lookup_array is the range of cells that contains the lookup values.
• return_array is the range of cells that contains the return values.
• 0 is the match type that specifies an exact match.

The logic behind this formula is:

• The EXACT function compares the lookup_value with each value in the lookup_array, and returns an array of TRUE and FALSE values, depending on whether they match exactly or not.
• The MATCH function looks up the value TRUE in the array returned by the EXACT function, and returns the relative position of the first match it finds.
• The INDEX function returns the value from the return_array based on the row number returned by the MATCH function.

Example

Using the same table of products as before, you want to find the price of the product “Apple” with an uppercase “A”. If you use the INDEX MATCH formula, such as:

=INDEX(B2:B7,MATCH(TRUE,EXACT("Apple",A2:A7),0))

You will get the correct result of $1.50, because it will compare “Apple” with each value in the range A2:A7 in a case-sensitive manner, and return the corresponding value from the range B2:B7.

Advantages and Disadvantages

The advantages of this method are:

• It can look up from any column to any column, meaning that the lookup value and the return value can be in any column of the table.
• It works in all versions of Excel, including Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2006.

The disadvantages of this method are:

• It requires an array formula, meaning that you have to press Ctrl + Shift + Enter to enter the formula, instead of just Enter. This can be cumbersome and prone to errors, especially for large formulas.
• It can only return one value per lookup, meaning that if there are multiple matches with the same case, it will return the first match it finds.

Alternative Approaches

Besides the three methods we have discussed, there are some alternative approaches to perform a case-sensitive lookup in Excel. These include:

• Using a helper column that converts the lookup values to a different case, such as uppercase or lowercase, and then using a standard lookup function on the helper column. This can be done by using functions like UPPER, LOWER, or PROPER. However, this approach requires modifying the original data, which may not be desirable or feasible in some cases.
• Using a user-defined function (UDF) that performs a case-sensitive lookup using VBA code. This can be done by writing a custom function in the Visual Basic Editor, and then using it in the worksheet. However, this approach requires some programming skills, and may not work in some environments, such as Excel Online or Excel for Mac.
• Using a third-party add-in that provides a case-sensitive lookup function. There are some add-ins available online that offer this functionality, such as Kutools for Excel or Ablebits Ultimate Suite. However, this approach requires installing and activating the add-in, which may not be possible or allowed in some cases.