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.
B.R.I.L.L.I.A.N.T. Tom!
Thank you!
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
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
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)
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.
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
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.
This is exactly what I was looking for. Thank you!
Thanks for your comment and thanks for following my Excel blog!
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.
Thank you, Steve – – and great improvisation to get the solution with all those ranges!
#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
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.