Last week I saw an interesting Excel puzzle from Leila Gharani on YouTube. Leila solved it using a sumproduct function. I made a video showing different solutions. Wasif Mustafa sent me his 3 helper column solution. Then Oz du Soleil created a video showing two solutions. So many ways to solve it!
Questions to Ask Before Solving
We can’t create the ideal solution unless we ask the right questions.
- Is this a one time task?
- How long would it take to complete manually?
- Should the solution be easy to understand?
- What version of Excel is being used?
- Can we re-arrange the data and then solve it?
- If it’s a long term solution, can we change the data input structure? (normalized format)
Follow along with Leila’s Excel file (solutions from me, Wasif Mustafa and Oz du Soleil).
Update: Excel file (zipped) with Geraldo Toledo’s vba solution!
Each ‘App’ listed in column H is found somewhere in columns B, C, or D. When found, we want to return the column header (the ‘Division’).
Sumproduct or Array
One formula solves it! Complex formulas for some but an expert can create it in a couple of minutes.
Leila’s Sumproduct formula =INDEX($B$4:$D$4,,SUMPRODUCT(($B$5:$D$45=H5)*(COLUMN($B$4:$D$4)-COLUMN($A$4))))
My Array formula =INDEX($A$4:$D$4,MIN(IF(H5=$B$5:$D$45,COLUMN($B$5:$D$45),””)))
Formula Helper Columns
Instead of 1 complex formula, what about breaking the solution down into easier to explain pieces? Some people have no interest in learning complex formulas but can follow carefully documented steps. I initially missed the fact that there can sometimes be multiple ‘Apps’ listed per row. This made it more challenging. It might not be an ideal solution but I got it to work!
Advanced Excel users might roll their eyes at a multi-step solution. Don’t forget that casual Excel users prefer understandable solutions.
1. helper (concatenate all text per row) =”,”&B5&”,”&C5&”,”&D5&”,”
2. get helper (lookup App, get helper value) =INDEX($E$5:$E$45,MATCH(“*,”&H5&”,*”,$E$5:$E$45,0))
3. Left of ‘App’ (display what’s to the left of ‘App’ in step2) =LEFT(I5,FIND(H5,I5)-1)
4. Count commas (count the commas in step3) =LEN(SUBSTITUTE(J5,SUBSTITUTE(J5,”,”,””),””))
5. Get Answer (index column headers using step4 value) =INDEX($B$4:$D$4,K5)
Each mini step can be explained to the casual Excel user.
Normalize Data using ALT D P
The data is not normalized so this makes any solution more challenging. Some people like to present the data in a matrix format but analyzing that data afterwards is often more challenging.
We could normalize the data quickly using the Pivot Table ALT D P trick. Once it’s normalized the index & match functions quickly solve it for us! If more data is added, it could be added in the new normalized structure. This way we don’t have to normalize the data or refresh a query ever again!
Normalize using GET & TRANSFORM
If you have Get & Transform (aka Power Query) you can create a simple query that will normalize the data. Then, a quick INDEX & MATCH formula solves it! End users can add more data in the original structure but they will need to refresh the query. After my video I added this solution in my Excel file and later on Oz du Soleil created a video as well. Oz loves Get & Transform!
This idea came from a YouTube viewer Wasif Mustafa. He sent me his solution and it’s great! Use a match formula like this: =MATCH(H5,$B$4:$B$45,0) for columns B, C and D. Then this IF statement =IF(ISNUMBER(M5),”Productivity”,IF(ISNUMBER(N5),”Games”,IF(ISNUMBER(O5),”Utility”,”App not found”))) in column I to get the answer!
I modified Wasif‘s idea and came up with another variation of this. Oz du Soleil had a “3 helpers in 1” idea along with an INDEX function (Oz used INDEX function not VLOOKUP!!!).
Manual Low Tech Solution
Currently, we have only 40 rows of data and we have 12 apps to find. We could use Excel’s Find feature to locate each ‘App’ and then copy/paste the column header. Not an exciting solution but it’s a quick and simple “Get ‘er done” solution.
Over the years, I have met a couple of VBA programmers that “think in code” like I think in formulas. They could easily create code to solve this. It would take me a long time.
UPDATE: Thanks to Geraldo Toledo who was kind enough to share his UDF solution (vba). Only 3 lines of vba code solves it!!
Function GetDiv(LookupVal As String, Titles As Range, SearchArea As Range) As String
GetDiv = Cells(Titles.Row, SearchArea.Find(LookupVal).Column)
Like in all cases, understand the requirements carefully (I initially missed that there can be more than 1 ‘App’ per row). Yes, listening is important but ask questions too! Then, before building the solution consider all relevant factors. Build the solution and teach it back to the person that you’re helping.
Thanks to Leila Gharani for the interesting puzzle and sumproduct solution, thanks to Oz du Soleil and Wasif Mustafa for their awesome solutions!!
Leila’s post, Leila’s video, Leila’s courses, Oz’s video, Oz’s LinkedIn post.
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’m a Data Analyst. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001.
There are so many amazing things that you can do with this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy learning Spanish, playing Chess, hanging out with Cali and Fenton and reading Excel books 🙂
Pingback: Complex Lookup: Find Header |
I was looking Leila Gharani video and follow a link to your site – congrats for the great material you produce!
I’ve seen your comment about a VBA approach for this puzzle and I thought about sharing the solution – you may be surprised about how easy it is (a single short line of code), below:
Function GetDiv (LookupVal As String, Titles As Range, SearchArea As Range) As String
GetDiv = Cells(Titles.Row , SearchArea.Find(LookupVal).Column)
The Find function does it all: returns the cell with the value as reference (object)…so you can easily do a lot afterwards!
Thanks for the vba solution! After adding the code is there anything else I need to do for it to work? (i.e. add named ranges?)
If I use the function like this =GetDiv(H5) (H5 being the value to look for) I get value errors. I’m missing something.
Anyway…thanks for reading my blog and for the code!
Thank you for the reply! I should have provided instructions 🙂
The function expects 3 parameters GetDiv ( LookupVal , Titles , SearchArea ) as below:
1st “LookupVal” is the value we are looking for ($H5 for the first App name)
2nd “Titles” is the range where we have the Divisions’ names ($B$4:$D$4)
3rd “SearchArea” is the area where we are going to look for the “LookupVal” : ($B$5:$D$45)
Formula filled for this example: =GetDiv($H5 , $B$4:$D$4 , $B$5:$D$45)
I hope it’s clearer now! Again, congrats for the great site and courses, it was great to somehow participate!
Thanks for the explanation! I got your UDF working and I’ve included it in my post above! Thanks for reading and participating!
Edit to my comment:
Although I’ve provided a solution using VBA as example, I would never use coding when a formula or data analysis resource can do…and I agree with your questioning on restructuring the data (at source or Power Query/Pivot) as it would make more sense. As a puzzle and for the fun they are all fine!
The best solution all depends on additional context. Sometimes rearranging the data really helps but it’s not always possible. It’s always good to know multiple ways to solve it including vba!
Pingback: Learn Excel VBA | My Spreadsheet Lab