Excel Magic Trick 1391: Case Sensitive VLOOKUP? 5 Examples of Excel Case Sensitive Lookup

Download Files:

Start File and Finished File:

See how to create a formula to perform Case Sensitive Lookup:
1. (00:10) Introduction
2. (00:16) VLOOKUP by itself will NOT work.
3. (01:19) Formula #1: SUMPRODUCT & EXACT functions to lookup Numbers based on a Case Sensitive Lookup Value. Array Formula that does not require Ctrl + Shift + Enter.
4. (04:14) Formula #2: LOOKUP & EXACT functions to lookup Text Names based on a Case Sensitive Lookup Value. Array Formula that does not require Ctrl + Shift + Enter.
5. (07:03) Formula #3: LOOKUP & FIND functions to lookup Text Names based on a Case Sensitive Lookup Value. Array Formula that does not require Ctrl + Shift + Enter.
6. (09:14) Formula #4: VLOOKUP, CHOOSE & EXACT functions to lookup Text Names based on a Case Sensitive Lookup Value. Array Formula that DOES require Ctrl + Shift + Enter.
7. (11:41) Formula #5: INDEX, MATCH & FIND functions to lookup Text Names based on a Case Sensitive Lookup Value. Array Formula that DOES require Ctrl + Shift + Enter.
8. (12:55) List of functions in Excel that can perform Array Operations in Array Formulas that do NOT Require Ctrl + Shift + Enter.
9. (13:26) Timing of Formulas
10. (14:13) Summary

[LeilaGharani]

A lot of fun and very interesting indeed! I like the combination of Lookup with FIND and Exact. Smart use of formulas. I also like the INDEX MATCH one. To avoid CSE for that one, we could wrap the FIND in the INDEX function: =INDEX(B28:B31, MATCH(1, INDEX(FIND(D28, A28:A31), , ))). Thank you for the Excel fun!

[JS-espo]

I got to say that I come back to this video everytime I need it. It might be better to actually learn and memorise how to do it but...

[Al-Ahdal]

Mike is an undisputed heavy weight when it comes to EXCEL (specially FORMULAS &

[haythamamairah]

I've read about some methods to perform case sensitive lookup.
But your methods are unique, in particular:
Formula #4: VLOOKUP, CHOOSE & EXACT

Thanks Mike :)

[mohamedchakroun]

Thank you Mike
I think that the speed of theses formulas depend on the profile of laptop according to my laptop is the faster and the rank 2 and 5 is the same as you.


Thanks a lot for all these instructive lessons

[familyvanbentum]

Thank you, very very helpful!! the case sensitive lookup for text is made my day!

[zaighamuddinfarooqui]

Brilliant ideas. Just "FUNTASTIC".

[jagadeeshkumar]

thank you very much it's a great help for my work

[puneetsonpal]

Really good, Can you make a Video for the Functions that can help Audit?

[Victor-ollo]

Thanks for the great video GrandExcelMasterMike !!! Good job!

[brosseauchristian]

Hi, Again an excellent video. Many thanks

[Sal_A]

Efficiency is the and FIND FTW!  Any chance you'll do some videos on Power Pivot CUBE functions?

[navinkumar]

Hi,
We can use the 4th solution without array function(ctrl+shift+enter) and it's work for me.

[GeertDelmulle]

Hey Mike, here's a small (slightly off-topic) question: is there a function that can flatten an array into a single collumn?
I want it to be a single cel formula thing to be used as part of an argument, just like you've done so many times...
Hope you read and respond to this (and have a solution). Thanks!

[DomingosCJM]

I liked the timing part even though there was no big difference.

[MrMatouuu]

Just what i was looking for. However I am having problem doing this in separate worksheets. Can you please explain how will I be able to do this using separate sheets?
I am using the formula
=IF(IFNA(VLOOKUP($A3, data, COLUMNS(Sheet1!$C3:Sheet1!C3)+2, FALSE), "")="", 0, IFNA(VLOOKUP($A3, data, COLUMNS(Sheet1!$C3:Sheet1!C3)+2, FALSE), ""))
to bring multiple values from sheet1 to other sheet using vlookup. It is working fine but there are two values Free Cash flow and Free cash flow (notice the capital 'C' difference) that is creating the problem. the Free cash flow (small letter one) is getting zero values. (Free Cash Flow capital C is has zero values already in sheet1 but Free cash Flow (small ones have some number values in sheet1 that is not transfered in my current sheet.
Please help this case.
Thanks

[AhmedAbdalalim]

cool

would you mind upload a video for accounting formulas?
thanks

[ubaidillahmuhammad]

nice job. please give me permission sir

[planxlsm]

1:00 Case Sensitive 7:28 FIND Case Sensitive