Performing a VLOOKUP Function with Case Sensitivity

In case our dataset has duplicate values with different case sensitivity, we need to perform a case-sensitive search to obtain a specific type of value. If the value being searched is not a numerical value, one can utilize an array formula to locate the row and then use an INDEX function to retrieve the value.


Question:

Is there anyone who can provide instructions on performing a case-sensitive vlookup in Excel?


Solution 1:


Dick Kusleika points out that the crucial aspect of finding a comprehensive solution is to ensure that the search does not halt upon detecting a case-insensitive match before the case-sensitive one.

The blog post titled “Case Sensitive
LOOKUP in Excel
” provides a solution to the problem through the utilization of INDEX and MATCH functions, as shared in a post by Peo Sjoblom. Additionally, there are alternative solutions mentioned in the comments section.


Solution 2:


To select specific matches, the more recent AGGREGATE┬╣ function can force non-matches into an error state and disregard them. To perform a case-sensitive lookup, this approach can be altered by using the EXACT function.

nam     date            code
bob     19-Oct-2015     001
BOB     02-Nov-2015     002
boB     28-Oct-2015     002
Bob     24-Oct-2015     004
bOB     27-Oct-2015     005
bOb     21-Oct-2015     006

Select both Bob and BOB from the assortment using…

=INDEX(B$2:B$7, AGGREGATE(15, 6, ROW($1:$6)/EXACT($A$2:$A$7, $E2), 1))

Fill down and right as necessary.

Case sensitive VLOOKUP


The AGGREGATE function is only accessible in Excel 2010 or later versions, and was not present in earlier versions.


Solution 3:

The feature of vlookup is such that the case of the text being compared is ignored. Hence, Bob and bob are considered the same. If you choose to use code() to convert the text to ASCII and perform the lookup, the process becomes more complex. Additionally, it is important to note that code() will only return the ASCII code for the first character in the string.


Solution 4:

In case your returned value is a numerical data instead of textual, and assuming that the initial column of your lookup_table is distinct (no repetition of ‘bob’), an array formula such as this can be utilized.

=SUM(EXACT(A3:A6,"bob")*(B3:B6))

To avoid issues, use Control+Shift+Enter instead of just Enter. The sum function will add up values in B3:B6 only if the corresponding cells in A3:A6 contain the exact text “bob”. As there is only one “bob” in the range, it will only sum up one cell. It is important to note that returning strings won’t work with the SUM function.

You wouldn’t want the sum of all values if ‘bob’ appears multiple times.


Update

In case the value you are searching for is non-numeric, you can utilize an array formula to locate the corresponding row, and then enclose it with an INDEX function. For instance, if your data is contained within A3:B6 and you wish to extract information from column B that corresponds to an exact match in column A.

=INDEX(A3:B6,SUM(EXACT(A3:A6,"bob")*(ROW(B3:B6)))-2,2)

Initiate with CSE, utilize the SUM function to locate the row that contains “bob”. Deduct 2 from the row number as the data begins from A3, which is one row less. Then, use the INDEX function to retrieve information from column B based on the previously obtained row number.

Frequently Asked Questions