Complex Lookup: Find Header

Find Header Based on Matrix Value

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.

Complex Lookup problem

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))),””)

Excel formula lookup multiple results

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.

Free Excel Download

The new Excel Dashboards course is here!

Now available on Udemy

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

10 Comments

  1. […] post, Leila’s video, Leila’s courses, Oz’s video, Oz’s LinkedIn […]

  2. Kevin Lehrbass August 23, 2017 at 2:02 am - Reply

    Hi 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 Gharani August 23, 2017 at 1:20 pm - Reply

      Many thanks Kevin :)

  3. Meni Porat August 26, 2017 at 1:47 am - Reply

    Hi 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 Gharani August 31, 2017 at 2:38 pm - Reply

      Thank you for sharing :)

  4. Micky September 9, 2017 at 10:29 pm - Reply

    Hi,
    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 Gharani September 10, 2017 at 4:58 pm - Reply

      Thank you Micky for your solution! Now we have even more ways of solving for these cases.

      • Micky September 11, 2017 at 7:10 pm - Reply

        Thank 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”.

  5. Arun Khanal September 16, 2017 at 1:52 pm - Reply

    Leila,
    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 Gharani September 19, 2017 at 12:49 pm - Reply

      Thanks Arun :)

Leave A Comment

Share This