

By definition, the VLOOKUP formula is not case-sensitive. Case-sensitive means, that it matters if you use capital letters or small letters. For instance, a VLOOKUP search for “AAA” will return the same value as for “aaa” or “Aaa”. But in some cases, you want to differentiate between capital and small letters. So how do you proceed? In this article, you learn how to make VLOOKUP, HLOOKUP, INDEX/MATCH and SUMIFS case-sensitive.
Contents
Example for all case-sensitive lookups in this article.
All following methods will be introduced with the example as shown in the image on the right-hand side. The cell range B4 to B19 contains codes (e.g. “AAA”, “AAa”, and so on) and the cell range C4 to C19 contains numeric values. In cell G3 you can insert a code (e.g. “Aaa”). The goal is to return the numeric value from the cells C4 to C19, depending on the code given in cell G3. For example if the code in G3 is “Aaa” you want to return the value from cell C6, which is 4,626.
Structure of the case-sensitive VLOOKUP formula.
The first method is based on the VLOOKUP formula. Unfortunately, the approach is a little bit complex and only works through a workaround. The structure of the case-sensitive VLOOKUP is shown in the image on the right-hand side.
The underlying idea is not to conduct a VLOOKUP with the actual search value, but rather with the row number, in which the search value can be found.
Structure of the case-sensitive VLOOKUP formula after inserting the fixed arguments.
If you insert the fixed arguments into this formula, you get the simplified structure of the VLOOKUP formula in the image on the right-hand side.
For our example, the case-sensitive VLOOKUP has the following parameters:
Putting these parameters into the formula structure of Figure 93, you get the following formula.
{=VLOOKUP(MAX(EXACT(G3,B4:B19)*(ROW(B4:B19))),CHOOSE({1,2},ROW(B4:B19),C4:C19),2,0)}
Example for the case-sensitive VLOOKUP formula.
A case-sensitive HLOOKUP works very similar to the VLOOKUP. The only difference is that you have to insert 3 TRANSPOSE formulas around and inside the CHOOSE formula. The reason is that CHOOSE can only handle vertical cell ranges in this case.
Simplified structure of a case-sensitive HLOOKUP formula.
The structure of the case-sensitive HLOOKUP formula is shown in the image above. As you can see, the formula is getting quite long. This might be a good example for a possible solution in Excel, but not necessarily the best one. The following method number 2 is usually a better option for a case-sensitive, horizontal lookup.
The arguments of the HLOOKUP formula are pretty much the same as in method 1a, the VLOOKUP formula. Applying this to a very similar example leads to the following formula.
{=HLOOKUP(MAX(EXACT(D7,C3:R3)*COLUMN(C3:R3)),TRANSPOSE(CHOOSE({1,2},TRANSPOSE(COLUMN(C3:R3)),TRANSPOSE(C4:R4))),2,FALSE)}
Example for a case-sensitive HLOOKUP formula.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
Structure of the case-sensitive INDEX/MATCH formula.
The case-sensitive INDEX/MATCH formula is compared to the case-sensitive VLOOKUP formula quite simple. The INDEX/MATCH formula is shorter and in many cases the better option.
The structure of the case-sensitive INDEX/MATCH formula combination is shown in the image on the right-hand side, the following number relate to the image.
Simplified structure of the case-sensitive INDEX/MATCH formula.
The structure shown in Figure 94 can be simplified. After inserting the fixed arguments, the INDEX/MATCH formula looks like in the image on the right-hand side.
Applying this structure on our example, you have the following parameters.
If you now put these parameters into the structure, you get the following formula.
{=INDEX(C4:C11,MATCH(TRUE,EXACT(G3,B4:B11),0))}
Example for the case-sensitive INDEX/MATCH formula.
Structure of a case-sensitive SUMPRODUCT formula.
The SUMIFS formula doesn’t support an exact lookup. That’s why you could use an alternative formula instead: SUMPRODUCT. Although no “real” array formula (with curly brackets), the SUMPRODUCT formula works like an array formula because it deals with arrays.
The structure of the case-sensitive SUMPRODUCT formula is shown in the image above. The SUMPRODUCT formula multiplies all arguments with each other and sums up the results. If a criterion is not fulfilled, the argument is 0 so the result of the multiplication is 0.
Because this formula is not an array formula, you don’t have to press Ctrl + Shift + Enter when finished typing.
=SUMPRODUCT(EXACT(G3,B4:B19)*1,C4:C19)
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
Was the information helpful in this article?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.