## Find Header based on Lookup Criteria in Matrix

### (Unique matrix values) – scroll down for non-unique method

In this tutorial I show you the answer to one of the questions that was asked some time ago on the Mr. Excel message board (the sample data was changed for the video) – The question is a complex LOOKUP problem – the task is to** find the header of the lookup criteria, which is in a matrix.**

One thing to keep in mind: The data set is unique – i.e. each value inside the matrix occurs once (scroll down below to see solutions if matrix values are not unique).

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.

### Watch the steps in this video:

## Other Eye-Opening Approaches

### 3 More Approaches…

Kevin Lehrbass from myspreadsheetlab.com shows other interesting solutions to this problem. What I specially like about Kevin’s approach in general is how he talks about the pro’s and con’s 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.

Show Column Header for Matrix Value | My Spreadsheet LabAugust 23, 2017 at 1:39 am[…] post, Leila’s video, Leila’s courses, Oz’s video, Oz’s LinkedIn […]

Kevin LehrbassAugust 23, 2017 at 2:02 amHi Leila,

This was a fun challenge! Here’s my post: http://www.myspreadsheetlab.com/video-00165-show-column-header-for-matrix-value/

Cheers,

Kevin

Leila GharaniAugust 23, 2017 at 1:20 pmMany thanks Kevin :)

Meni PoratAugust 26, 2017 at 1:47 amHi Leila,

Here’s my solution using IF, IFNA and MATCH:

=IF(IFNA(MATCH($H5,$B$5:$B$45,0),0),$B$4,IF(IFNA(MATCH($H5,$C$5:$C$45,0),0),$C$4,IF(IFNA(MATCH($H5,$D$5:$D$45,0),0),$D$4)))

Best Regards,

Meni Porat

Leila GharaniAugust 31, 2017 at 2:38 pmThank you for sharing :)

MickySeptember 9, 2017 at 10:29 pmHi,

How about something like at the attached picture ?

(No Helper clolumn needed).

http://tinypic.com/view.php?pic=sym51i&s=9#.WbRrA3S3yJI

Michael (Micky) Avidan

MS Excel MVP (2009-2018)

Leila GharaniSeptember 10, 2017 at 4:58 pmThank you Micky for your solution! Now we have even more ways of solving for these cases.

MickySeptember 11, 2017 at 7:10 pmThank you, Leila,

I must point out that even thoe TEXTJOIN seems to be a nice idea – it is restricted to Ecxel 365 users only.

If you open such a file and try to alter that formula in a non :365″ version – you rnd ud with an error.Addition to that

addition to tht I DO “hate” using Hlper Columns whereevver they can be avoided and it is essential to mention that multilying a Section of a formula “n” times – the time that equals the criterias – is, to my opinion, also “bad prctice”.

Arun KhanalSeptember 16, 2017 at 1:52 pmLeila,

Your Solution can be minimized with less arguments as under:

=INDEX($B$4:$D$4,,SUMPRODUCT(($B$5:$D$45=H5)*(COLUMN($A$4:$C$4))))

Note: (COLUMN($B$4:$D$4)-COLUMN($A$4)

is replaced by

(COLUMN($A$4:$C$4)

Leila GharaniSeptember 19, 2017 at 12:49 pmThanks Arun :)

Technical JitendraJanuary 7, 2018 at 6:28 amHi, Leila Gharani, I am a big fan of your Channel, I’m From India, and I’m also making Excel related videos on YouTube.My YouTube Channel Name is “Technical Jitendra”, My Videos all videos in Indian Language “Hindi”. My Subscribers are less and I cross 10,000 views, but Youtube yet reviewing…… My goal is Excel Expert. I’m doing a job as National MIS Report in India. could You Help me with Increasing Subscribers and Views Please…Leila Gharani.

VALENTINESeptember 23, 2018 at 3:14 pmThanks Leila, your videos are very good. And I must say you are a good tutor

ImanApril 27, 2019 at 12:52 pmHi Leila and thank you for your great videos.

May I ask you in this specific example how did you input the data in cells H5 to H16? Are they manually copied from the matrix?

BR,

Iman

Leila GharaniMay 3, 2019 at 6:39 amHi Iman – yes, they were a copy and paste from the original data.