This tutorial explains how to make Excel's VLOOKUP function case sensitive, shows some other formulas that recognize text case, and points out the advantages and limitations of each function.

I think every user using Excel knows what the function in Excel is doing vertical lookup. That's right, it's VLOOKUP. However, few people know that Excel's VLOOKUP is case-insensitive, meaning it treats lowercase and uppercase letters as the same characters.

Here is a quick example showing VLOOKUP's ability to be insensitive to text. Suppose you enter "bill" in cell A2 and "Bill" in cell A4. The following formula will capture "bill" because it ranks first in the lookup array and returns the matching value in B2.

=VLOOKUP("Bill", A2:B4, 2, FALSE)

Later in this article, I will show you how to make VLOOKUP case sensitive. We will also explore several other functions that can perform case-sensitive matching in Excel.

Case sensitive VLOOKUP formula ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

As mentioned above, the conventional VLOOKUP formula does not recognize letter case. However, there is a way to make Excel's VLOOKUP case sensitive, as shown in the following example.

Suppose you enter the item ID in column A and want to pull the price and comments of the item from columns B and C. The problem is that the ID includes upper and lower case characters. For example, the value in A4 (001Tvci3u) and the value in A5 (001Tvci3U) are only different in the last character, namely "u" and "U".

When looking for "001Tvci3 U ", the standard VLOOKUP formula outputs $90 related to "001Tvci3 u " because it is ahead of "001Tvci3 U " in the lookup array. But that's not the result you want, right?

=VLOOKUP(F2, A2:C7, 2, FALSE)

To perform case-sensitive lookups in Excel, we use VLOOKUP, CHOOSE and EXACT functions in combination:

VLOOKUP(TRUE, CHOOSE({1,2}, EXACT( Find value , find array ), return array ), 2, 0) This general formula works perfectly in all cases. You can even look up from right to left, which is something that the regular VLOOKUP formula cannot do. Thanks to Pouriya for coming up with this simple and elegant solution!

In our case, the actual formula is as follows.

To pull the price in F3:

=VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(F2, A2:A7), B2:B7), 2, FALSE)

To get comments in F4:

=VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(F2, A2:A7), C2:C7), 2, FALSE)

Notice. In all versions of Excel except Excel 365, this can only be used as an array formula, so remember to press Ctrl Shift Enter to do it correctly. In Excel 365, it can also be used as a regular formula due to support for dynamic arrays.

How this formula works:

The core part is the CHOOSE formula with EXACT nested:

CHOOSE({1,2}, EXACT(F2, A2:A7), C2:C7)

Here, the EXACT function compares the values ??in F2 with each in A2:A7 and returns TRUE when they are exactly the same (including letter case), otherwise returns FALSE:

{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

For the index_num parameter of CHOOSE, we use the array constant {1,2}. As a result, the function combines the logical values ??in the above array and the values ??in C2:C7 into a two-dimensional array, as shown below:

{FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}

The VLOOKUP function takes over from there, looks up the lookup value (i.e. TRUE) in the 2D array (denoted by the logical value) and returns the matching value from the 2nd column, i.e. the price we are looking for:

VLOOKUP(TRUE, {FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}, 2, 0)

Case sensitive XLOOKUP formula

Microsoft 365 subscribers can perform case-sensitive lookups in Excel using simpler formulas. As you can guess, I'm talking about the XLOOKUP function, the stronger successor of VLOOKUP.

Because XLOOKUP operates to find and return arrays separately, we do not need two-dimensional array techniques like the previous example. Simply use EXACT as the search array parameter:

XLOOKUP(TRUE, EXACT( Find value , find array ), return array , "Not Found") The last parameter ("Not Found") is optional. It simply defines the value returned when no match is found. If you omit it, a standard #N/A error will be returned when the formula cannot find a match.

For our sample tables, these are the case sensitive XLOOKUP formulas we use.

To get the price in F3:

=XLOOKUP(TRUE, EXACT(F2, A2:A7), B2:B7, "未找到")

To extract comments in F4:

=XLOOKUP(TRUE, EXACT(F2, A2:A7), C2:C7, "未找到")

How this formula works:

Like the previous example, EXACT returns an array of TRUE and FALSE values, where TRUE represents a case-sensitive match. XLOOKUP looks for the TRUE value in the above array and returns the match from the return array . Note that if there are two or more exactly the same values ??in the lookup column (including letter case), the formula returns the first found match.

Restrictions of XLOOKUP : Available only in Excel 365 and Excel 2021.

SUMPRODUCT - Case sensitive lookup for returning matching numbers

As the title shows, SUMPRODUCT is another Excel function that can perform case-sensitive lookups, but it can only return numeric values . If this doesn't fit your needs, skip to the INDEX MATCH example, which provides a solution for all data types.

As you probably know, Excel's SUMPRODUCT function multiplies the components in the specified array and returns the sum of the products. Since we want to do case-sensitive searches, we use the EXACT function to get the first array:

=SUMPRODUCT((EXACT(A2:A7,F2) * (B2:B7)))

Unfortunately, the SUMPRODUCT function cannot return a text match because the text values ??cannot be multiplied. In this case you will get a #VALUE! error in the F4 cell in the screenshot below:

How this formula works:

Like the VLOOKUP example, the EXACT function compares the values ??in F2 with all values ??in A2:A7 and returns TRUE on case-sensitive matches, otherwise returns FALSE:

SUMPRODUCT(({FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}*{155;186;90;54;159;28}))

In most formulas, Excel evaluates TRUE to 1 and FALSE to 0. Therefore, when SUMPRODUCT multiplies elements at the same position in two arrays, all non-matches (FALSE) become zero:

SUMPRODUCT({0;0;0;54;0;0})

As a result, the formula returns the number in column B corresponding to the exact case sensitive match in column A.

SUMPRODUCT limit : Only numeric values ??can be returned.

INDEX MATCH - Case-sensitive lookup for all data types

Finally, we are about to get an unlimited case-sensitive lookup formula that works on all Excel versions and all datasets.

This example is placed at the end not only because the best is left to the end, but also because the knowledge you gained in the previous examples may help you better understand the case-sensitive MATCH INDEX formula.

The combination of INDEX and MATCH functions is often used in Excel as a more flexible and versatile alternative to VLOOKUP. The following article explains how these two functions work together well - using INDEX MATCH instead of VLOOKUP.

Here, I will only remind you some key points:

• The MATCH function searches for the search value in the specified search array and returns its relative position.
• The relative position of the search value is passed directly to the row_num parameter of the INDEX function, guiding it to return a value from the row.

To make the formula recognize text case, you just need to add a function in the classic INDEX MATCH combination. Obviously, you need the EXACT function again:

INDEX ( return array , MATCH(TRUE, EXACT( find value , find array ), 0)) The actual formula in F3 is:

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

In F4, we use the following formula:

=INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0))

Remember that in all versions except Excel 365, it can only be used as an array formula, so make sure to enter it by pressing Ctrl Shift Enter at the same time. If done correctly, the formula will be enclosed in braces, as shown in the screenshot below:

How this formula works:

Like all previous examples, EXACT returns TRUE for each value in A2:A7 that exactly matches the value in F2. Since we use TRUE as the lookup value for MATCH, it returns the relative position of the exact case-sensitive match, which is exactly what INDEX needs to return the match from B2:B7.

Advanced case sensitive search formulas

The INDEX MATCH formula mentioned above looks perfect, right? But in fact, it is not perfect. Let me tell you why.

Assume that the cell corresponding to the search value in the return column is blank. What should the formula return? Return nothing. Now, let's see what it actually returns:

=INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0))

Oh, the formula returns zero! Maybe this doesn't matter when only text values ??are processed. However, if your worksheet contains numbers and some of them are real zeros, that's a problem.

In fact, all other search formulas discussed earlier are expressed in the same way. But now you want a perfect formula, don't you?

To make the case-sensitive INDEX MATCH formula absolutely perfect, you can wrap it with an IF function that checks if the return cell is empty and in this case returns a null value:

=IF(INDIRECT("C"&(1 MATCH(TRUE,EXACT(A2:A7, F2), 0)))"", INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0)), "")

In the above formula:

• "C" is the return column.
• "1" is a number that converts the relative position of the cell returned by the MATCH function into the actual cell address .

For example, the lookup array in our MATCH function is A2:A7, which means that the relative position of cell A2 is "1", because this is the first cell in the array. But in reality, looking for the array starts at line 2. To compensate for the difference, we add 1, so the INDIRECT function will return the value from the correct cell.

The screenshot below shows the actual effect of the improved case-sensitive INDEX MATCH formula.

If the return cell is empty, the formula outputs an empty string:

If the return cell contains zeros, the formula returns 0:

If you would rather show some messages when the return cell is empty, replace the empty string ("") with some text in the last parameter of the IF:

=IF(INDIRECT("C"&(1 MATCH(TRUE, EXACT(A2:A7, F2), 0)))"", INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0)), "沒有可返回的內(nèi)容，很抱歉。")

Easily perform case-sensitive VLOOKUP

Our users of the Ultimate Excel Suite have a special tool that makes finding in large and complex tables easier and stress-free. The best thing is that the merge two table tools has a case-sensitive option, and the following example shows how it actually works.

Suppose you want to pull Qty from the lookup table based on the unique product ID. into the main table :

All you have to do is run the Merge Table Wizard and perform the following steps:

1. Select the main table to pull new data.
2. Select a lookup table to find new data.
3. Select one or more key columns (in our case the item ID). And make sure to check the case sensitive match box.
4. The wizard will walk you through the remaining three steps, where you specify which columns to update, which columns to add, and select some additional options as needed.

After a moment, you will get the desired result :)

This is how to do a lookup that takes into account text case in Excel. Thank you for reading and hope to see you on our blog next week!

Exercise workbook download

Case sensitive VLOOKUP example (.xlsx file)

The above is the detailed content of How to do case-sensitive Vlookup in Excel – formula examples. For more information, please follow other related articles on the PHP Chinese website!