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

Подписаться 2,7 млн

Просмотров 236 тыс.

50% 1

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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.
⬇️ Download the workbook here: pages.xelplus.com/sumproduct-...
🔑 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?
Video for Index Match Basics: • How to use Excel Index...
To understand Sumproduct: • How to Use SUMPRODUCT ...
✔ Alternate Solutions ✔
Watch Kevin's video to see 4 different approaches of solving this
2 with array formulas, 1 with helper columns and last one with a pivot table to normalize the date set: • 00165 Show Col Header ...
Watch Oz's Video from Excel on Fire here which shows how to solve this with a COUNTIFS function and the Power Query method: • Retrieve The Correct C...
To solve for multiple matches - i.e. non-unique data set, check out this video: • Excel Formula Lookup M...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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

Опубликовано:

16 авг 2017

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 305

@LeilaGharani 6 месяцев назад

Grab the file I used in the video from here 👉 pages.xelplus.com/sumproduct-index-file

@vickydsouza4252 4 года назад

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.

@albumR 5 лет назад

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!

@GosCee 3 года назад

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

@barrysedlik1535 Год назад

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!

@philipmuirhead3351 5 лет назад

I was struggling to find formula to look up both a column and row heading based off a list of top 10 values from a table... this video gave me the clues to figure it out!!! Thank you!

@shrirangshende7024 5 лет назад

Very Nice explained Match and Index function I watch your videos just from one week and your presentation and simple understanding English is very good thanks

@jamiemer1109 3 года назад

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.

@ice8433 2 года назад

1 year later, you saved me a major headache. bless you

@BertandRussell 4 года назад

Hi Leila, Your videos are really great. Probably this is best and most advanced Excel RU-vid channel in the world...I really grateful to you. Thanks a lot. God bless you.

@fidget2020 5 лет назад

Hi Leila, I have watched many of your videos & I am so impressed with your delivery - it’s simple, clear and without fluff. You launch straight into the problem & solution without mucking around. I suppose it takes a lot more effort than it appears, but I’m curious, do you prepare/write a script and rehearse, or are you pretty much ad-libbing it as you go? I realize that you know your material inside out and upside down, but still, your videos and presentation seem so effortless! Keep them coming, please!

4 года назад

Excellent!!! This is the first time I see the most complex lookup like that 🤔🤔😆

@LeilaGharani 7 лет назад

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

@LeilaGharani 7 лет назад

Link to 2 other videos that solve for non-unique data sets: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ULa7nQrMvoc.html and this one: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-TMZEUlFGp1U.html

@joynalabedin6814 5 лет назад

Thanks a lot for this video.

@vimalarammohan7345 4 года назад

@@LeilaGharani =IF(COUNTIF(INDIRECT($I$4),H5)=0,"",$I$4). . Is this formula okay mam

@gauthamn18 4 года назад

Damn that's definitely the second problem I'm facing, two days under one date and I need both of them mentioned

@lgoopio 3 года назад

My headers are dates, and i need to return the value under the most recent date. Date progresses to the right. So the rightmost is the most current.

@markrowell4480 3 года назад

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 clouds..... thank you so much!

@LeilaGharani 3 года назад

You're very welcome, Mark!

@babbleon4826 3 года назад

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.

@saunokchakrabarty8384 5 лет назад

Brilliant video. Loved it. Thanks for explaining everything so clearly. Combining all of this makes work incredibly easier.

@LeilaGharani 5 лет назад

Thank you Sanouk. I’m glad you like the tutorials.

@therodgepodge002 6 месяцев назад

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.

@LeilaGharani 6 месяцев назад

Perfect! Glad you got it sorted out.

@AdamKaczmarek 4 года назад

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

@LeilaGharani 4 года назад

That's great! I'm glad to hear that.

@vps99999 7 лет назад

Awesome work Leila... Your concepts are very much clear and you are teaching how to approach in solving the problem... Amongst the best tutorials for sure!!!

@LeilaGharani 7 лет назад

Thank you very much Vinay for the kind words. Am very happy to hear you find the videos useful.

@jastonkong1409 4 года назад

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

@medadarkasirga2913 3 года назад

you can also add zero to it. ex. =sumprodcut(0+(array1),array2)

@alexandergerlach4579 5 лет назад

You are Amazing! Your videos helped me out a lot! THANK YOU!

@DanielPeraalta 4 года назад

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!

@LeilaGharani 4 года назад

I'm glad it was helpful. Great to have you here :)

@matiascampiani9156 2 года назад

Hi Leila, amazing video!! Great explanation and it solved the problem I had. Thanks so much!

@ricardoluizmarcello Год назад

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

@DH-sf8lx 7 лет назад

simply brilliant!! Thank you for sharing your knowledge.

@LeilaGharani 7 лет назад

You're very welcome D H :)

@sumanchanda3950 7 лет назад

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

@LeilaGharani 7 лет назад

That's great! now you have many different solutions :) thanks to get the online Excel team.

@jollyjuln1054 Год назад

All the best for you, Leila. Thank you for sharing your knowledge.

@LeilaGharani Год назад

Thank you!

@didrechsler330 7 лет назад

Thanks Leila, I had no idea that you could use SUMPRODUCT for text! It's solved a long standing problem for me.

@LeilaGharani 7 лет назад

You're welcome Di Drechsler. Very happy to hear that!

@jerry171460 4 года назад

Good video. I watched it twice already and I took note. I do like the thought process before the actual approach to the solutions. Asking oneself to see what the correct and logical solution to the problem is brilliant. The double " --" and the minus column($a$4) were explained clearly! I like the "reasons" of why they are included/excluded in the formula.

@LeilaGharani 4 года назад

Glad you enjoyed it!

@kuldeep.3012 3 года назад

Mam, you are such a great teacher.

@partymaschine92 4 года назад

I really really like all of your videos! I‘ve learned so much. I like the way you solve problems by using the target function and looking what parameters have to be dynamic. However in this case, I think it may work also by using PowerQuery and trying to unpivot the datasets. Am I right ?

@sachinrana184 4 года назад

I appreciate your effort. Very helpful video.✌️

@maurobellotti 4 года назад

Awesome... I'd never had this figured out all by myself. Mind blowing. Thanks a lot

@LeilaGharani 4 года назад

Glad you like it Mauro.

@sachinrv1 4 года назад

Fantastic video Leila :) This is the most groovy video on SUMPRODUCT I've ever seen..

@LeilaGharani 4 года назад

Thank you Sachin!

@vivek1416 5 лет назад

Excellently you have taught the video..Truly amazing madam..Keep helping us in getting to learn many such amazing functions in excel.thank you.

@LeilaGharani 5 лет назад

I'll definitely do that. Glad you find the video helpful.

@michaelkim9258 4 года назад

This is next level. Thanks so much.

@johng5295 3 года назад

Your method is the best after comparing it with Ken and Oz de Soleil.

@eduartshqiptari 3 года назад

Excellent! Many thnx for being so helpful

@3BoxCreation 2 года назад

great video leila.. thank you so much ♥️♥️

@ram-it.damn-it 3 года назад

This is genius excel master.

@michaelbethel8081 4 года назад

WHAT IF SAY "BLEND" APPEARS MORE THAN ONCE UNDER "PRODUCTIVITY" THEN INSTEAD OF "1" IT WOULD COUNT "2" AND GIVE THE WRONG COLUMN WHATS A WORK AROUND OR SOLUTION TO THIS?

@akshaybhammar6756 3 года назад

Thanks for your help, it really helped me a lot

@krn14242 6 лет назад

Great approach.

@chamindabasnayake4844 7 лет назад

Hi Leila, Brilliant lesson, Thank you.

@LeilaGharani 7 лет назад

You're welcome Chaminda.

@singhvaranasi 6 лет назад

awesome and very helpful, thanks

@jayacharya7333 5 лет назад

Wow... Great use of Sum Product. Enjoyed it. Thanks..

@LeilaGharani 5 лет назад

You're very welcome Jay. Glad you liked it.

@jamesreed8314 2 месяца назад

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.

@jingwu8461 5 лет назад

Great. Hope one day I can use it at work.

@Scaw 4 года назад

Truly excellent. And I thought that I knew Excel well......!

@julietthornton6628 4 года назад

Awesome! A great way of looking at the problem and so beautifully explained! This is literally the best excel video I've seen in terms of clarity and delivery :-) In the scenario I'm working on, I need to do this with a partial match/contains. e.g. where you are looking at 'blend' in column H, I would be looking at a text string like 'my blend' or 'blend magic' - essentially the word 'blend' could be anywhere in that string so we want to find the first instance of a word in the array partially matching the text string. Anyone got any bright ideas?

@dhunpagla3871 6 лет назад

Super helping.... Thanks for sharing such knowledge with us 👌

@LeilaGharani 6 лет назад

You're welcome Dhun. Through sharing and questions from the community, I also learn. So that works out well :)

@uchennaanunike4214 11 месяцев назад

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

@TheMhujako 4 года назад

This is really a great help! You nailed it! :)

@LeilaGharani 4 года назад

I'm glad to hear that :)

@tonytinderal 6 лет назад

Great Video!

@MySpreadsheetLab 7 лет назад

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.

@LeilaGharani 7 лет назад

Thanks Kevin! Helper column is a good option - I also like the pivot table idea :)Thanks for sharing.

@rockguitarist8907 7 лет назад

Hi Kevin. Can you please further explain the helper column idea? If you CONCATENATE columns B thru D, your helper column is now the column. And I thought the idea was to get the division each text is part of. Some of the rows have two text strings in them, so you'd have to use SEARCH as part of INDEX/MATCH as well. Please help explain bc I'd love to learn what you and Leila are thinking!

@MySpreadsheetLab 7 лет назад

rockguitarist8907 hi rockquitarist! I will take a look at this tomorrow and maybe do a video. Difficult to beat Leila's sumproduct solution. And you're right that with possibly two items per row it complicates using a helper column. Cheers, Kevin

@rockguitarist8907 7 лет назад

That would be really great if you could do a video!

@MySpreadsheetLab 7 лет назад

I'm editing my video now!

@abdelkrimmesaiahmed662 3 года назад

Great video thank you

@ashish291191 4 года назад

Simply Amazing :)

@mahadevshah3099 4 года назад

Superb.....I have become Fan of your skill, I have watched my videos from many instructers but what u did @ 6:59 is out of the box. These helpful trick are never explained. Never seen such output. I will definitely enroll you adv excel course in Udemy, I have already enrolled for your VBA course in Udemy. Thank u and God bless u. Thank

@LeilaGharani 4 года назад

I'm glad to hear that. Many thanks for your support of my courses.

@wangmengfan568 5 лет назад

Very helpful!!!!!!!!!!!!!!!!!!!!!!

@raghuv7114 4 года назад

Thank you and well received

@alexandonian1273 7 лет назад

Great video. This is very useful!

@LeilaGharani 7 лет назад

I'm glad to hear that Alex.

@KC_47. 4 года назад

I always watch your video.. You are super genius!! The way u think, so amazing! I'll always support your channel! Pliz upload more videos!

@LeilaGharani 4 года назад

Thank you so much 😀

@dipakacharya6903 7 лет назад

Great. Love the way u make us understand.

@LeilaGharani 7 лет назад

Thank you. You're very welcome Dipak.

@KS-qc4lo 4 года назад

Oh man! This video saved my ass on a project. Thanks!!!!

@LeilaGharani 4 года назад

Glad to help Kevin :)

@scheekostudio6574 4 года назад

Thank you very much. It’s superb and really helpful for my work.

@LeilaGharani 4 года назад

You are most welcome

@ah244895 4 года назад

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!

@wliu330 3 года назад

Even though this video is 3 years old, it's way more advanced than what I would learn at uni... thank you!! I do have a follow up question if I may? What if one of the apps belonged to multiple categories, say Productivity and Utility? I have a similar spreadsheet but can't figure out how to do a summary because i have duplicate values... (replace date column with user names, and app category with event names... and instead of app names I have role names like trainer or lead trainer... that's pretty much my spreadsheet!) I'd be grateful for any hints on this! Thank you!

@farmerwang1973 4 года назад

Awesome!!!

@xlschool7334 4 года назад

very complex I did'nt know what you did any your super intelligent teacher

@yurd563 5 лет назад

very grateful for the video!

@LeilaGharani 5 лет назад

I'm glad the video is helpful.

@arghoshj 3 года назад

Awesome 👏

@marcosmarcelino4912 2 года назад

Thanks very much!!!

@asmrindia 3 года назад

Amazing Mam

@user-bo6mn6ch2p 2 года назад

Good go ,please keep up

@mohamedchakroun4973 7 лет назад

Thanks you leila great video :-)

@LeilaGharani 7 лет назад

You're very welcome Mohamed.

@PROHDGaming 7 лет назад

Awesome video Leila

@LeilaGharani 7 лет назад

You're very welcome.

@RakeshSaha1705 3 года назад

I love the way you explain the logic to choose any formula. Really a great job you are doing.

@huszone 6 лет назад

Thank you for sharing.

@LeilaGharani 6 лет назад

You're welcome.

@lihibiran8648 5 лет назад

You have great videos @Leila Gharani !!Thank you. Would you also share a method to lookup the latest date in a table according to multiple criteria match in different columns other then the date?

@lisaclark744 2 года назад

THANK YOU

@majidsiddique8227 5 лет назад

very excellent

@engrmonirulislammcips4753 2 года назад

Great !!

@esbi1124 3 года назад

Hello, just super.

@HistorianTrevi 5 лет назад

Thanks for the video! Very clear as always! I hope I won't annoy you if I ask a question. Finally I found in this tutorial a table resembling the one in my spreadsheet so perhaps I'm able to explain clearly: suppose you have numbers instead of app names, how can I get two (or one or all depending on the table row composition) results by matching only the date? In other words I'm trying to input a date and get the results for that row in the non-blank columns. I've tried looking for v, hlookups and index(match) functions returning multiple results but I can't get it to work (moreover, all tables in the tutorials I followed were without blanks). Thanks in advance to whoever can help!

@LAUGHLOUNGESHORTS 4 года назад

i love you you helped me so much

@LeilaGharani 4 года назад

Glad to help.

@frankyroussos1352 3 года назад

First of all I would like to thank you a lot for learning new functions like INDEX, MATCH & SUMPRODUCT. Excellent work! My solution would be: (I have my source table in cells I3 to K21 with labels from I3 to K3 and my search criteria in cells M3 to N10 with labels from M3 to N3) in cell P4 I wrote the formula =IF(ISERROR(VLOOKUP($M4;I$4:I$21;1;FALSE));0;1) in cell Q4 the formula =IF(ISERROR(VLOOKUP($M4;J$4:J$21;1;FALSE));0;2) and in cell R4 =IF(ISERROR(VLOOKUP($M4;K$4:K$21;1;FALSE));0;3) then in cell S4 I add the three together =SUM(P4:R4) and come up with either number 1, 2 or 3. Then just a simple =VLOOKUP(S4;$P$12:$Q$14;2;FALSE) in cell T4 after using a small reference table for 1 Productivity, 2 Games, 3 Utility in Cells P12:Q14. Finally I drag/copy the formulas down from P4:T4 up to P10:T10 and voila! This way I may use more columns but I also solve problems like repitting values as raised by Mr. Michael Bethel below.

@F002boy 7 лет назад

Sounds like this is the solution I am looking for, thank you, Leila!

@F002boy 7 лет назад

Thanks a lot again, Leila!! Index + Subproduct is indeed what solved my problem However, in my database, sometimes "Blend" appears in more than one column, so it adds up the two matched number and returned a wrong column result... In my case, the ideal result is have 2+ cells showing the 2+ matched columns... Can you please please please teach me how I can do that? Thanks so much!!

@LeilaGharani 7 лет назад

Hi F002boy - Yes - this solution is for cases when you are sure you have unique values. In case not, you need to use a different approach. I can think of replacing SUMPRODUCT with AGGREGATE together with the LARGE function to get the first occurrence, then second occurrence and so on.... I can make a video on this and post as soon as I get a chance...

@F002boy 7 лет назад

Noted. Yes, please make this video when you get a chance, I have been stuck on this for a very long time, it will be super duper helpful in many ways if I can do this. Many Many thanksss, Leila!!

@LeilaGharani 7 лет назад

Sure. Would gladly do so. It should be out on Wednesday.

@F002boy 7 лет назад

THANK YOU SO MUCH!!

@hosseinhosseinpoor4845 3 года назад

very good

@VS-rh8rq 7 лет назад

Sumproduct better approach.i was using index with a match within index to isolate column where the look up value was..This simpler way..thank you

@LeilaGharani 7 лет назад

Hi Vimal - I was also first thinking of the Index within a match to isolate the column and then sumproduct occurred to me :)

@Belttonne1 Год назад

I have homework to do now! ❤

@sweetpoison007 6 лет назад

awsome stuff

@LeilaGharani 6 лет назад

Thanks. Glad you like it.

@zhaozhang4047 4 года назад

Can you still use MATCH function to combine 3 columns (i.e., Productivity, Games and Utility) but with "CTL + SHIFT + ENTER" in this case?

@muhammadfarooq891 6 лет назад

Incredibly awesome, Leila please share please make video on countif or counta adjoined with dates.

@LeilaGharani 6 лет назад

I have sumifs with dates. Countifs will work in a similar way. Check it out here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-XIhbL20jTHc.html

@Hey_Delight 5 лет назад

A load of thanks. 😉

@LeilaGharani 5 лет назад

You're very welcome. It was my delight :)

@s.y.daniel2137 Месяц назад

thanks, just what I needed today ! Thought it was simple to look up a matrix to return the header. with MS 365, is there an easier way to do?

@kasus4485 5 лет назад

Of course I like this video, and of couse I already subscribed it!

@LeilaGharani 5 лет назад

I am glad you like it and welcome to the community!

@kasus4485 5 лет назад

@@LeilaGharani Well explanation... I'm waiting for another tips and trick!

@kohyeechang2980 5 лет назад

Hi Leila, your video is awesome and amazing. Can i know if there I were to simply lookup for "AA", what is the result could be? I tried for working file, it will auto default to a header....How can i ensure i will turn out as blank?

@omtechlife6621 6 лет назад

You R awesome ....

@LeilaGharani 6 лет назад

Glad you like the videos :)

@chelebarry 6 лет назад

How would you subtract in case you had more columns, say productivity or the table headers start in c2 or d2?

@N_TALK 7 лет назад

Thanks for the Awesom video Leila Gharani....

@LeilaGharani 7 лет назад

You're very welcome.

@N_TALK 7 лет назад

Leila Gharani Please share your email id i have a problem with data velidation function which i use in Inventory managment (in FIFO) there is some problem. The problem is write here in the comment box it is not possible.

@LeilaGharani 7 лет назад

best is to post your specific question either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others....