This is how the data set looks (columns A to D) – the challenge is to solve for I5 to I16.
What makes this complex? The lookup value could be in any column and any row.
My Approach: Use INDEX & SUMPRODUCT
The formula I used to solve this was a combination of INDEX with SUMPRODUCT:
Cell I5 = INDEX($B$4:$D$4,, SUMPRODUCT(($B$5:$D$45=H5)*(COLUMN($B$4:$D$4)-COLUMN($A$4))))How does the formula work?
INDEX was my first choice here because it has no problems to return text. The first argument of INDEX is the array – which basically is the area where the answer could be. The answer could be either of the column headers so that’s the only part that needs to be marked.
The next argument is row – we only have 1 row so we can skip this argument.
Now columns: This is the key here. We need to figure out which column the app is in and return this information to the INDEX function.
SUMPRODUCT can help us do just that – and without the need for CSE (control shift enter). This part of the formula
($B$5:$D$45=H5)returns a matrix of TRUE and FALSE values depending on if the app that’s being looked up is inside the matrix or not. This part of the formula:
(COLUMN($B$4:$D$4)-COLUMN($A$4))generates a matrix that looks like:
{1,2,3}When we multiply the TRUE & FALSE matrix with the {1,2,3} matrix we turn the FALSE values to zero and TRUE values to either 1, 2 or 3 depending on the column the app is sitting in.
Other Eye-Opening Approaches
3 More Approaches…
Kevin Lehrbass from myspreadsheetlab.com shows other interesting solutions to this problem. What I especially like about Kevin’s approach in general is how he talks about the pros and cons of each of his methods.
Array formula using Index & Min:
=INDEX($A$4:$D$4,MIN(IF(H5=$B$5:$D$45,COLUMN($B$5:$D$45),””))) (CSE)Formula helper columns using INDEX, LEFT & SUBSTITUTE.
Normalize data with a Pivot Table using Alt D P.
You can get more details and download the workbook here and watch the video here.
2 More Approaches…
Oz du Soleil from Excel on Fire posted a video with two more approaches. One method used COUNTIFS as a helper column and the other approach uses Power Query to normalize the data set. If you have Power Query this is a super fast option. Make sure you watch this video.
1 More Approach…
Bill Szysz from YouTube came up with an amazing solution! This one is so simple, so smart and so genius! It’s an array solution so it does require CSE, and requires Excel 2013 or above. I never would have thought of using TEXTJOIN to solve this. Here is the solution:
=TEXTJOIN(",", ,IF(H5=$B$5:$D$45,$B$4:$D$4,"")) (CSE)Scroll all the way down to watch the video on this.
How would you get the header based on a lookup value that could be in any cell?
What is your approach?
Excel Formula Lookup Multiple Results
How can we handle cases with multiple matches?
My approach to this is to use a combination of the INDEX & AGGREGATE functions. The large argument of AGGREGATE allows us to extract different matches one by one and return them to INDEX without requiring CSE.
INDEX & AGGREGATE to Return Multiple Answers
This is the formula:
=INDEX($B$4:$D$4,, AGGREGATE(14,4,($B$5:$D$45=$H5)*(COLUMN($B$4:$D$4)-COLUMN($A$4)), COLUMNS($I$4:I4)))How does the formula work?
INDEX is used as before to define the range of possible answers. The AGGREGATE function filters out the largest position first and returns this to index. When the formula is copied to the next cells, the second position is returned to index. In case the position is zero, the result will be an error.
How to account for the error:
You have 2 options;
To wrap the formula in an IFERROR function- which should be fine as long as you don’t have huge data sets;
or a better option is to use a simple IF formula to figure out if the INDEX formula should run or not. This formula would be:
=IF(COLUMNS($I$4:I4)<=$G5, INDEX($B$4:$D$4,, AGGREGATE(14,4,($B$5:$D$45=$H5)*(COLUMN($B$4:$D$4)-COLUMN($A$4)), COLUMNS($I$4:I4))),””)
G5 is a helper cell which uses the COUNTIF function to figure out how many times the lookup value occurs in the matrix. The formula for G5 is:
=COUNTIF($B$5:$D$45,H5)With this method you can be sure that your formula works with unique or non-unique data sets.
Watch the steps in this video:
TEXTJOIN Formula
Excel’s TEXTJOIN Function can Solve Complex Lookup Problems
This solution is from Bill Szysz: he uses the TEXTJOIN function to return multiple answers:
=TEXTJOIN(",", ,IF(H5=$B$5:$D$45,$B$4:$D$4,"")) (CSE)I explain the mechanics of this formula in the video below.
In addition he takes the formula one step further and accounts for cases when a matrix value is repeated in the same column. This avoids duplicate answers:
=TEXTJOIN(", ",, IF(ISNUMBER(FIND($B$4:$D$4,TEXTJOIN(",", , IF(H5=$B$5:$D$45,$B$4:$D$4,"")))), $B$4:$D$4,"")) (CSE)With the help of the FIND and ISNUMBER functions, we can ignore duplicate results.
Watch the video to understand how this formula works:
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
