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)

Excel File

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!

Challenge

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!

3 Helpers

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.

VBA 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)
End Function

Summary

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!!

Links

Leila’s post, Leila’s video, Leila’s courses, Oz’s video, Oz’s LinkedIn post.

About Me

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 🙂