#### Case-Sensitive VLOOKUP

In this Excel lesson, you will teach yourself how to create a case-sensitive vlookup. This is a clever way to solve many problems caused by caps lock keyboard button.

## Case-sensitive vlookup

By default, VLOOKUP is not case sensitive, meaning that it treats uppercase and lowercase letters as the same. For example, if you use VLOOKUP to search for the value “apple” in a table that contains “Apple”, it will return the value in the same row.

Vlookup function doesn’t care about case-sensitivity. So what to do when you do?

This is an example:

The task is to vloookup the value to the word. Formula should care about case-sensitivity.

The solution is this array formula:

**=VLOOKUP(value,IF(EXACT(value,column),table),column_number,0)**

0 here means the exact match for the vlookup function.

Formula in the example is:

**{=VLOOKUP($D2,IF(EXACT($D2,$A$2:$A$10),$A$2:$B$10),2;0)}**

Please be aware that it is an array formula so you need to use **CTRL + SHIFT + ENTER** keyboard shortcut. That’s why there are braces around the formula. They appears after you typed CTRL + SHIFT + ENTER.

Keep in mind that this approach requires you to use additional columns and functions, which may not be as efficient as a simple VLOOKUP formula.

However, it gives you more control over the matching process and allows you to perform case-sensitive lookups when necessary.

## Case-sensitive lookup

The problem is that your data differ only by case so capitalization is important for you. That’s why you need case sensitive lookup. Let’s construct a formula which will solve your problem. INDEX formula suits the best.

Syntax here will be:

**=INDEX(value_you_need, row_number, column_number)**

**value_you_need**is a data range from your data table**row_number**– this is hard to get. You have to use EXACT function for case sensitive function**column_number**is always 1

In this example you need the **Price** and you have **Product ID**s. The formula you need is:

=INDEX(B1:B5;SUMPRODUCT((EXACT(A1:A5;E2))*(ROW(A1:A5)));1)

## Leave a Reply