Case Sensitive Lookups

Posted on September 7th, 2015 in Excel Howtos - 3 comments

We all know that VLOOKUP (and its cousins MATCH, HLOOKUP and LOOKUP) are great for finding information you want. But they are helpless when you want to do a case-sensitive lookup.

case-sensitive-vlookup-in-excel

So how do we write case sensitive VLOOKUP formulas?

Simple. We can use EXACT formula.

What exactly is the EXACT formula?

EXACT formula checks if 2 cells have exactly the same value. And it is very SenSITive.

For example, =EXACT("this","THIS") will be false , where as =”this”=”THIS” will be true.

Using EXACT formula to do case sensitive lookups

Let’s say the value you are looking up is in cell F4, the lookup range is B5:C11 (column B has lookup value and column C has value you want).

You can use EXACT formula along with INDEX + MATCH or SUMPRODUCT to do case sensitive lookup. Let’s look at each of these variations:

Using EXACT & INDEX + MATCH formulas to do case sensitive lookups:

Formula: {=INDEX($C$5:$C$11,MATCH(TRUE,EXACT($F$4,$B$5:$B$11),0))}

How it works? 

Let’s go from inside out.

EXACT(F4, B5:B11) portion: This will return an array of TRUE & FALSE values. Something like this:

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

MATCH(TRUE, EXACT(...), 0) portion: Now we look for TRUE in all the values EXACT has returned. This will be 3 (since 3rd value in the array is true).

INDEX(C5:C11, MATCH(...)) portion:  This will simply return the 3rd value in the column C, ie an exact match.

{INDEX(...)}:  Because this is an array formula, you must press CTRL+Shift+Enter after typing it. The {} indicates this.

Related: Learn about INDEX+MATCH combination.

Using EXACT + SUMPRODUCT formula:

If the lookup result is a number (or date) and there is only matching value, you can use SUMPRODUCT to do case sensitive lookups.

Related: Introduction Excel SUMPRODUCT formula.

Formula:=SUMPRODUCT(EXACT($F$4,$B$5:$B$11) * ($C$5:$C$11))

How it works?

The EXACT(F4, B5:B11) portion returns a bunch of TRUE & FALSE values.

When you multiply these TRUE & FALSE values with column C (which contains numbers), the end result will be the value you are looking for.

This is possible because in Excel, TRUE is 1 and FALSE is 0. So when you multiply a list of logical values (true / false) with a list of numbers, everything that corresponds to false becomes 0.

So we get,

{0;0;30;0;0;0;0}

SUMPRODUCT simply adds up these numbers and returns 30 as result.

Note: This formula won’t work if you have text values in column C or more than one TRUE in EXACT result (ie multiple values match the lookup criteria).

For advanced users: SUMPRODUCT – Advanced scenarios

Download case sensitive lookup – example workbook

Please click here to download case sensitive lookup example workbook. Examine the formulas to learn more about this technique.

More ways to lookup:

Get The VLOOKUP Book: If you are always looking for help about VLOOKUP, look no further. Get my book, it’s going to make you awesome in VLOOKUP, INDEX+MATCH, multi-condition lookups, 2 way lookups and more. Click here to order your copy.

How do you write case sensitive lookups?

Let me be honest. I haven’t had a single case sensitive lookup scenario in last year. But email from a reader prompted me to research this problem.

What about you? Do you often deal with case-sensitive data? How do you write case sensitive lookups? Please share your tips & formulas in comments section.

 

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

3 Responses to “Case Sensitive Lookups”

  1. jason m says:

    Alternative:

    =LOOKUP(2,1/EXACT(F4,B5:B11),C5:C11)

  2. Gijs says:

    I have a related question, hopefully someone can figure this out:

    Is it possible to do a MATCH on TWO columns?
    I want to copy the SalesRep name for an account in a specific country (eg IBM Germany). So i would have to do an INDEX with a MATCH on both ACCTNAME and ACCTCOUNTRY.

    Can that be done?
    Thanx!

  3. Asheesh says:

    @Gijs

    You should ideally post your question in the forum...however, one of the possible solutions for your question is

    INDEX(SalesRep,MATCH(1,(Country_Range="Germany")*(Account_Range="IBM"),0))

    To be entered with CTRL + SHIFT +ENTER

    There are several methods explained in the below link

    http://dailydoseofexcel.com/archives/2009/04/21/vlookup-on-two-columns/

Leave a Reply