On October 17 we celebrated spreadsheet day. It was a day full of excitement around the globe! Crowds gathered around spreadsheets…dancing and drinking occurred into the wee hours of the morning. Part of the celebration included a lookup formula challenge from Debra Dalgleish (spreadsheet day creator).
I saw Deb’s “lookup formula challenge” in her newsletter. Get Deb’s Excel file here or here (FN0052). Click here to see the solutions listed below.
What is the formula challenge?
We want to see which Customer text value is found inside each Imported Data text value.
‘Joe & Co’ is found inside of cell D2 so Code ‘JOE001‘ goes into cell E2. The assumption is that all of the text values in column D have one and only one match. More on this later on!
I thought that I would create a video to explain her solution and maybe create a solution of my own. Little did I know how much I would learn from the amazing online Excel team.
Video 00168 Deb’s Solution & My Solution
In this video (apologies for the low volume) I explained Deb’s formula:
=INDEX($B$2:$B$4,MATCH(1,IFERROR(SEARCH(“*”&$A$2:$A$4&”*”,D2,1),0),0))
I also created and explained how my formula works:
=INDEX($B$2:$B$4,MIN(IF(ISNUMBER(SEARCH($A$2:$A$4,D2)),ROW($A$2:$A$4)-ROW($A$2)+1,””)))
Deb’s solution is really interesting due to the wildcards *. My formula is dangerous if no matches are found as it defaults to the first answer! See ‘Bill Szysz Words of Wisdom’ below.
Within a few hours Ankur Shukla responded with this comment:
I hadn’t even considered using the LOOKUP function. Video 00169 explains this amazing solution!
And then Bill Szysz adds his double match formula! I’ve never seen this approach before!
Later on pmsocho shared his INDEX MATCH COUNTIF solution! Watch his video here (in Polish).
Video 00169 Ankur Shukla Solution
This video explains Ankur’s solution. It uses the SEARCH function but the LOOKUP function can handle arrays without ‘Ctrl’ ‘Shift’ ‘Enter’. Also, his formula is fast and short! The downside? The binning trick must be explained carefully.
XLarium commented with his alternative lookup: =LOOKUP(9^99,SEARCH($A$2:$A$4,D2),$B$2:$B$4)
Video 00170 Bill Szysz Solution
Bill’s array formula uses the INDEX function, two MATCH functions and wildcards. The last MATCH function is using 0 for exact match. The * (asterisks) do the magic! Watch it here.
My video includes a comparison of the various solutions.
Bill Szysz Words of Wisdom
Remember the original assumption? Each ‘Imported Data’ text value will have one and only one ‘Customer’ found inside of it. What if there’s more than one found?
As Bill mentions, if there are multiple matches the LOOKUP solution returns the last matching value. The MATCH solutions return the first value found. This is a very important distinction!
Bill’s comment led me to this question: What if there’s nothing found?
All solutions (except mine) return an error if nothing is found. In my solution if nothing is found the false part of the array is activated. Index function converts “” (blank) to zero which returns the first value (cell B2).
I later changed the “” to 999999 so that it returns an error not a default answer. If you want a default answer then maybe my solution isn’t so bad after all (you could also wrap the other solutions with IFERROR and type a default answer).
Which Formula is the Fastest?
Jonathan’s question is very important! Imagine a data set with 200000 rows. Compex/slow formulas can easily cause Excel to freeze and crash.
Thanks to pmsocho for timing the solutions! With 3000 rows of data, Ankur’s LOOKUP solution takes 0.01 seconds!
Learn more about calculation performance. Thanks for the links Pmsocho
- msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-improving-calcuation-performance
- msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-performance-and-limit-improve
- https://fastexcel.wordpress.com/
Conclusion
Thanks to Debra Dalgleish for the formula challenge! It’s interesting that the challenge was named “Lookup Formula Challenge” and Ankur’s LOOKUP based formula was the fastest and shortest solution!
Thanks to everyone who participated: Debra Dalgleish, Ankur Shukla, Bill Szysz, Piotr Majcher, rockguitarist8907, learnexcel, XLarium, Jonathan Cooper, Oz du Soleil, Leila Gharani, Gerson – Minhas Planilhas, Dixon Peer.
About Me
I’ve been working with data since 2001. It’s my career and also a hobby! When I’m not playing around with data, playing chess or doodling I’m probably watching Netflix with Cali & Fenton.
