Excel Complex Lookup: Find Header based on Lookup Criteria in Matrix with INDEX & SUMPRODUCT

Discover the solution to a challenging lookup problem in Excel, ideal for those who manage data in a matrix format. This video provides a detailed guide for creating reports that require to find the header of the lookup criteria which is in a matrix layout.

🔑 Key Points:
- Matrix Data Structure: Understand the structure of the dataset – a matrix with dates on the left, divisions as column headers, and a list of apps within.
- Lookup Challenge: Learn how to create a report listing apps and their corresponding divisions, where apps are scattered across various columns and rows.
- Utilizing the Index Formula: Explore using the Index formula for its ability to return text, crucial for identifying division names.
- Dynamic Column Identification: Discover how to dynamically identify the correct column for each app using a unique approach.
- SUMPRODUCT Function: Learn the use of the Sum-Product function to handle matrix data and return the appropriate column index.
- Step-by-Step Explanation: Follow a clear, step-by-step process, including setting up the formula, fixing cell references, and expanding the range for accurate results.

In this video 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 - task is to find the header of the lookup criteria which is in a matrix. The lookup value could be in any column and any row - that's what makes the problem complex. How to get the header based on a lookup value that could be in any cell? In the video you'll find my approach;

It uses the INDEX function together with SUMPRODUCT. What is your approach?

✔ Alternate Solutions ✔

Watch Kevin's video to see 4 different approaches of solving this



🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[vickydsouza]

Dear Maam,

I thought I was good at excel .... that was until i started watching your videos. Never fail to learn new things from you. Index with Sumproduct. Amazing.

[jamiemer]

Hey Leila, I have discovered that using SEQUENCE(1, COLUMNS(Header Range) is a real handy way rather than COLUMN(Header Range) because the TRUE FALSE Values from the first part of the formula would always be multiplied by another array that always starts at 1 and goes to however many columns the table has. This is really handy if the leftmost column of the table does not start in the A Column.

[barrysedlik]

Leila, I used your Excel Complex Lookup formula to find the correct header in a two-dimensional array. What an elegant solution! Thank you for solving this vexing problem!

[sumanchanda]

A week back, one of my colleagues approached me with the same problem and was searching for the solution and here I find it....Thanks Leila....and a special shout to Bill for a shorter solution....

[therodgepodge]

Thank you so much, Leila! This was just what I was looking for. I just had to adapt the logic to tables, and it works like a charm.

[gauravvrasttogi]

I am encountering this problem 7 years after you solved it 😂. Thank god, you are there. 😊

[albumR]

This video is so great Leila, thanks for sharing. I spent about an hour struggling to find a solution for exactly this problem. You saved my day. Much appreciated!

[uchennaanunike]

Good one @LeilaGharani, what I want using your matrix, is an spill formular that will return the division of the game instead of the game. So, the result of the spill is where sloops is, productivity will be returned, also where is productivity will also be returned, and where fightrr is, games will be returned, twister will also return games. All these in one formular

[babbleon]

i was good at using index() and sumproduct() separately to query data from tables but this is outside the box. I've gone through a number of your tutorials and the way you combine basic excel functions creatively to form elegant solutions is exactly what i have been looking for. There aren't many other sites that deal with the handling of data arrays within excel functions in such an easy-to-understand way. The other vid that uses Aggregate() is also brilliant. can i also request that you show any creative uses of OFFSET() and COUNTIF() i.r.o. arrayed data, that you might have in your arsenal. Please keep up the good work and many thanks.

[LeilaGharani]

I got some queries regarding how to handle cases when there is more than one match per category - so when the data set is not unique - I'll be posting a solution for that this week that uses the AGGREGATE function. Stay tuned....

[GosCee]

Leila, you're good! Genius! The more I dig into you previous videos, the more gold I find. 😊 Thank you

[AdamKaczmarek]

I have been battling a problem for hours in my complex spreadsheet, and your video helped me solve it! Thank you!

[BertandRussell]

Hi Leila,

Your videos are really great. Probably this is best and most advanced Excel YouTube channel in the world...I really grateful to you. Thanks a lot. God bless you.

[markrowell]

I enjoy your teaching style/mannerisms. I actually learn things in addition to what you're teaching (like evaluating the formula).

I've spent a long time trying to grasp INDEX MATCH with significant frustration. Your 2 videos parted the thank you so much!

[ah]

I recreated your problem and solved it, but with a twist, that I think simplifies it slightly.
=INDEX($I$3:$K$3, 1, SUMPRODUCT(($I$4:$K$15=M5)*{1, 2, 3}))


I replaced your columns portion with a hard coded {1, 2, 3}


More fun than watching TV!

[ricardoluizmarcello]

Leila, you will never know how much you helped me….

[jastonkong]

wow.... I didn't know what was the double minus sign for "- - " until now. Thanks for explaining it in such an easy way to understand. Once again, you made learning Excel fun and easy! Kudos!!! :D

[DanielPeraalta]

Leila i'm very thankful for your videos, I thought I wouldn't be able to do what I wanted to do, and without your videos it was true! But after watching for of them about Index and match I finally was able to pull this off. You just gained one more subscriber for life, keep up the good work!

[MySpreadsheetLab]

SUMPRODUCT is so incredibly useful! Great video Leila! In this challenge I suppose the idea is that you can't change the data layout? If I were in a hurry I would either create an array formula or a helper column to concatenate text values from columns B, C, D. Then a quick index/match. But then again, your sumproduct doesn't require CSE. One final option would be to quickly normalize using the ALT D P pivot trick.

[jamesreed]

This video helped me so much. I was trying to return dates from a dynamic calendar against two arguments. The INDEX function paired with the match functions is the way to solve it, XLOOKUP was getting confused. Thank you Leila.