Tom’s Tutorials For Excel: Case Sensitive LOOKUP

Tom’s Tutorials For Excel: Case Sensitive LOOKUP

Here’s how you can attack a lookup action for a unique list of items, when the only difference among the characters in the list is their upper or lower case.



The criteria item to lookup is in blue-colored cell D5.
The formula in the pink-colored cell E5 is
=INDEX(B5:B20,MATCH(TRUE,EXACT(D5,A5:A20),0)).
This is an array formula. Array formulas are entered by simultaneously pressing the Ctrl+Shift+Enter keys. The curly braces are not entered by you; Excel places them automatically when the array formula is properly entered.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
14 comments on “Tom’s Tutorials For Excel: Case Sensitive LOOKUP
  1. Karthik says:

    B.R.I.L.L.I.A.N.T. Tom!

  2. George says:

    Thanks TOM

    I never came across @ contrl+shit+enter procedure so far in excel
    It is an eye opener,will you be able to explain more abt this contrl+shift+enter,in what scenarios are we suppsoed to use this

    • Tom Urtis says:

      These are called array fromulas. Lots of info about them on the web if you search keywords “excel array formulas” (without the quotes). Start with my video about them below, then look at some examples on line from your searches to get a feel for how they work.

      Here’s the link to my video about array formulas:
      http://www.youtube.com/watch?v=Yr_xuLB5JHs

      • George says:

        Thanks Tom the array formulas video was informative and thorough,would try and use in work..

        Just as a comment ,know you are explaining array formulas in video,sumproduct function are an alternative to array in sum (example)

        • Tom Urtis says:

          Thanks for watching the video. Regarding SUMPRODUCT, yes it would be an alternative in that case, as it would be in many cases where arrays are used. My objective with that video was to keep it as short as possible while introducing the concept of arrays. I felt that if I went into the video taking about arrays, and ended the video talking about SUMPRODUCT, it would have resulted in a confusing mix, where the basic points about arrays would have been lost. Maybe I’ll do a SUMPRODUCT video next, now that you mention it.

  3. Marty L says:

    Not sure how useful this is – typically you are using VLOOKUP against multiple rows in a column against another table: that is FOR EACH CELL in col A you will want to do a lookup against another table (i.e. for a description in a master table for example). AN array formula would result in a circular reference

    • Tom Urtis says:

      Thanks for following my blog and for your comment.

      I honestly don’t know what the point of your comment is. VLOOKUP would not work in this case because the entry variations in each cell are based on the cases of individual characters. Also, VLOOKUP can be used in many ways not regarded as “typical” by more Excel users than you may realize.

      And, this example *is* of an array formula, clearly not resulting in a circular reference error.

      As for how useful this example is, there are many cases when analysis must be done on data that was produced by inconsistent manual entry, not in a clean standardized entry format that was validated at its source. Business offices all over the world are faced with needing to make quick analysis functions on inconsistently entered data. This use of the INDEX, MATCH, and EXACT functions is one more tool in a user’s toolbox that can help get the job done easier.

  4. Steve Austrin says:

    Hey Tom. Great example. It went a long way to solving a problem I was having. I modified your result a bit because I had to reproduce the array formula on multiple rows and across multiple columns. The required absolute references blew it up so I used range names instead and Voila!

    Awesome! Thanks again.

  5. GEORGE says:

    #VALUE! aTlAs
    atlas 2.00
    ATLAS 3.00
    aTlAs 45.00
    ATLAS 4.00
    aTLAS 5.00 F2-FORMULA INDEX(C3:C7,MATCH(TRUE,EXACT(I2,B3:B7),0))
    INDEX(B5:B20,MATCH(TRUE,EXACT(D5,A5:A20),0)).

    why

  6. Tom Urtis says:

    Thanks for visiting my website.

    The formula works for me.
    You would get a #VALUE error if you only tried to enter the formula using the Enter key.
    See the last paragraph in my original post, below the picture.
    This is an array formula.
    It must be committed to the cell using Ctrl+Shift+Enter.

1 Pings/Trackbacks for "Tom’s Tutorials For Excel: Case Sensitive LOOKUP"
  1. […] VLOOKUP Week, Excel MVP Tom Urtis shows how to do a case-sensitive lookup. This is a great trick! Read Entire Article VLOOKUP will never differentiate ATLAS from aTlas. Tom's formula can. Share […]

Leave a Reply to Karthik Cancel reply

Your email address will not be published. Required fields are marked *

*