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.
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....
Link to 2 other videos that solve for non-unique data sets: th-cam.com/video/ULa7nQrMvoc/w-d-xo.html and this one: th-cam.com/video/TMZEUlFGp1U/w-d-xo.html
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!
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.
Hi Leila, Your videos are really great. Probably this is best and most advanced Excel TH-cam channel in the world...I really grateful to you. Thanks a lot. God bless you.
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....
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!
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!
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
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!
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.
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
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.
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!
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.
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.
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!
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
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!!!
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!
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
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!
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
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 ?
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.
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!
Nice spin on Index and Sumproduct doing a 2 way lookup (replacing Match with Sumproduct). Interesting how you find new uses for familiar functions (which is fantastic). I could see, though, that you would have a problem if you had duplicate values in your original data. I think you would have to have some sort of mega array formula to display all the values, don't you think?
Hi Michael - thanks for you comment. Yes - this is for cases when you are sure you have unique values, otherwise like you say you would run into problems. If you have more than one occurrence, I would probably use the AGGREGATE and the LARGE function inside of INDEX. I'll make a video on this approach as soon as I get a chance :)
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?
I'm having trouble when using this but with table values such as table[#headers], etc... what would you recommend? My case is basically identical to this one but I want it to have the dynamic function that tables grant so I can move things around etc. and not cause errors.
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!!
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...
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!!
Hi Leila, should we use sumproduct function for sure...any other ways we can solve for this result. But in the process thanks that we were glad we were exposed to usage of sumproduct, F9 and copying them to another field to monitor them.
Brilliant work Combination of index with sumproduct could be very powerful. I thought of some how using 3 Hlookup ( for the 3 columns) with iferror and if function to identify the column location. But still your idea is more accurate
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?
Hello this is a great explanation. In my situation I have a matrix with random numbers. In a separate cell I am calculating a certain value. I want excel to look for the NEAREST value I calculated in the matrix and then tell me the corresponding column and row header. I would greatly appreciate if you can explain.
Can you please help - I have stores in first column and 600 items listed for each store in 600 columns. I need to get list of items header if the value under any of the item columns for a particular store is greater than 0.
Hi Leila, sorry for such a basic question, but how does the column function know to return the correct column value when the reference argument is the array? I can see that the first part of the sumproduct function knows the position of the matching value, but how does the column function know the position?
Hi Tracy - that's not a basic question actually. Arrays took me a while to understand too. The column function returns numbers. So column(B4) returns a 2 since B is column number 2. Inside the array, I refer to COLUMN($B$4:$D$4) which returns {2,3,4} and then I deduct column($A$4) which returns 1. This leaves me with column number {1,2,3} which I can use in the Index function to find the right column. Hope this makes a little sense?
Hi Leila how would you sumproduct where the criteria is in another column. Eg a table with two columns A1:A10, B1:B10 and criteria in D1:D3. I want to sum the values in B that matches the criteria in A. Thanks
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?
Good day. I would like to ask what to do if my lookup value inside sumproduct is a result of another excel function. I initially tried to put "value" before the lookup value but it only works for number, unfortunately I have look up values that is a combination of letters and number.
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!
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.
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....
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?
Grab the file I used in the video from here 👉 pages.xelplus.com/sumproduct-index-file
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.
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....
Link to 2 other videos that solve for non-unique data sets: th-cam.com/video/ULa7nQrMvoc/w-d-xo.html and this one: th-cam.com/video/TMZEUlFGp1U/w-d-xo.html
Thanks a lot for this video.
@@LeilaGharani =IF(COUNTIF(INDIRECT($I$4),H5)=0,"",$I$4). . Is this formula okay mam
Damn that's definitely the second problem I'm facing, two days under one date and I need both of them mentioned
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.
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!
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.
1 year later, you saved me a major headache. bless you
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.
Perfect! Glad you got it sorted out.
Hi Leila,
Your videos are really great. Probably this is best and most advanced Excel TH-cam channel in the world...I really grateful to you. Thanks a lot. God bless you.
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....
That's great! now you have many different solutions :) thanks to get the online Excel team.
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!
Leila, you will never know how much you helped me….
Leila, you're good! Genius! The more I dig into you previous videos, the more gold I find. 😊 Thank you
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!
You're very welcome, Mark!
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
I have been battling a problem for hours in my complex spreadsheet, and your video helped me solve it! Thank you!
That's great! I'm glad to hear that.
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!
I'm glad it was helpful. Great to have you here :)
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.
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
you can also add zero to it. ex. =sumprodcut(0+(array1),array2)
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.
Mam, you are such a great teacher.
I am encountering this problem 7 years after you solved it 😂. Thank god, you are there. 😊
All the best for you, Leila. Thank you for sharing your knowledge.
Thank you!
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!
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.
Glad you enjoyed it!
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.
Thanks Kevin! Helper column is a good option - I also like the pivot table idea :)Thanks for sharing.
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!
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
That would be really great if you could do a video!
I'm editing my video now!
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!!!
Thank you very much Vinay for the kind words. Am very happy to hear you find the videos useful.
Excellent!!! This is the first time I see the most complex lookup like that 🤔🤔😆
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!
This is genius excel master.
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
I'm glad to hear that. Many thanks for your support of my courses.
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!
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
Brilliant video. Loved it. Thanks for explaining everything so clearly. Combining all of this makes work incredibly easier.
Thank you Sanouk. I’m glad you like the tutorials.
Thanks Leila, I had no idea that you could use SUMPRODUCT for text! It's solved a long standing problem for me.
You're welcome Di Drechsler. Very happy to hear that!
Your method is the best after comparing it with Ken and Oz de Soleil.
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 ?
Excellently you have taught the video..Truly amazing madam..Keep helping us in getting to learn many such amazing functions in excel.thank you.
I'll definitely do that. Glad you find the video helpful.
Fantastic video Leila :) This is the most groovy video on SUMPRODUCT I've ever seen..
Thank you Sachin!
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?
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.
great video leila.. thank you so much ♥️♥️
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
Hi Vimal - I was also first thinking of the Index within a match to isolate the column and then sumproduct occurred to me :)
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!
You are great , leila , I'd lost much by not following you earlier .
In case of my bad English , I apologise .
No worries. It's great to have you here!
Hi Leila, amazing video!! Great explanation and it solved the problem I had. Thanks so much!
Oh man! This video saved my ass on a project. Thanks!!!!
Glad to help Kevin :)
Awesome... I'd never had this figured out all by myself. Mind blowing. Thanks a lot
Glad you like it Mauro.
simply brilliant!! Thank you for sharing your knowledge.
You're very welcome D H :)
I love the way you explain the logic to choose any formula. Really a great job you are doing.
I appreciate your effort. Very helpful video.✌️
Two questions I have for you Leila: What if the same app appears 2 times in the same column? And how would it look like also to look up the date?
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!
Thank you so much 😀
Excellent! Many thnx for being so helpful
Nice spin on Index and Sumproduct doing a 2 way lookup (replacing Match with Sumproduct). Interesting how you find new uses for familiar functions (which is fantastic). I could see, though, that you would have a problem if you had duplicate values in your original data. I think you would have to have some sort of mega array formula to display all the values, don't you think?
Hi Michael - thanks for you comment. Yes - this is for cases when you are sure you have unique values, otherwise like you say you would run into problems. If you have more than one occurrence, I would probably use the AGGREGATE and the LARGE function inside of INDEX. I'll make a video on this approach as soon as I get a chance :)
I think that would be awesome Leila. Are you on holiday?
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?
Hi Leila, Brilliant lesson, Thank you.
You're welcome Chaminda.
This is next level. Thanks so much.
Wow... Great use of Sum Product. Enjoyed it. Thanks..
You're very welcome Jay. Glad you liked it.
I'm having trouble when using this but with table values such as table[#headers], etc... what would you recommend? My case is basically identical to this one but I want it to have the dynamic function that tables grant so I can move things around etc. and not cause errors.
Sounds like this is the solution I am looking for, thank you, Leila!
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!!
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...
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!!
Sure. Would gladly do so. It should be out on Wednesday.
THANK YOU SO MUCH!!
Hi Leila, should we use sumproduct function for sure...any other ways we can solve for this result. But in the process thanks that we were glad we were exposed to usage of sumproduct, F9 and copying them to another field to monitor them.
Amazing Mam
Brilliant work
Combination of index with sumproduct could be very powerful.
I thought of some how using 3 Hlookup ( for the 3 columns) with iferror and if function to identify the column location. But still your idea is more accurate
In some case if the data have some duplications in different columns what could solution to bring more than one header ?
hmmmmm.... I have to think about that....
You're welcome Abdelrahman. The HLOOKUP methods sounds very creative....
You are Amazing! Your videos helped me out a lot! THANK YOU!
Super helping.... Thanks for sharing such knowledge with us 👌
You're welcome Dhun. Through sharing and questions from the community, I also learn. So that works out well :)
Great approach.
I have homework to do now! ❤
This one is again mind-blowing video you share...
I THink you are devloper of Excel??
I wish! Just a user like you :)
@@LeilaGharani
I wish just my all teacher 💔 Like you...
Great video thank you
This is really a great help! You nailed it! :)
I'm glad to hear that :)
Great video. This is very useful!
I'm glad to hear that Alex.
Truly excellent. And I thought that I knew Excel well......!
Incredibly awesome, Leila please share please make video on countif or counta adjoined with dates.
I have sumifs with dates. Countifs will work in a similar way. Check it out here: th-cam.com/video/XIhbL20jTHc/w-d-xo.html
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?
Great. Love the way u make us understand.
Thank you. You're very welcome Dipak.
Hi, it was very useful, but like the same I need to know how to match a number, instead of words "Blend" says if "1" or "0.5".
Awesome 👏
Thank you and well received
Hello this is a great explanation. In my situation I have a matrix with random numbers. In a separate cell I am calculating a certain value. I want excel to look for the NEAREST value I calculated in the matrix and then tell me the corresponding column and row header. I would greatly appreciate if you can explain.
very grateful for the video!
I'm glad the video is helpful.
Thank you very much. It’s superb and really helpful for my work.
You are most welcome
Great. Hope one day I can use it at work.
Hello, just super.
Can you please help - I have stores in first column and 600 items listed for each store in 600 columns. I need to get list of items header if the value under any of the item columns for a particular store is greater than 0.
What is formula if you look up for the date instead at the left?
very complex I did'nt know what you did
any your super intelligent teacher
Hi Leila, sorry for such a basic question, but how does the column function know to return the correct column value when the reference argument is the array? I can see that the first part of the sumproduct function knows the position of the matching value, but how does the column function know the position?
Hi Tracy - that's not a basic question actually. Arrays took me a while to understand too. The column function returns numbers. So column(B4) returns a 2 since B is column number 2. Inside the array, I refer to COLUMN($B$4:$D$4) which returns {2,3,4} and then I deduct column($A$4) which returns 1. This leaves me with column number {1,2,3} which I can use in the Index function to find the right column. Hope this makes a little sense?
Awesome video Leila
You're very welcome.
Leila Hi,
So, what should be done in case of repeated naming in the same columns? The index number turns 2 or 3... instead of 1 or 2.
Do you have any better option plz tell us
Great Video!
Hi Leila how would you sumproduct where the criteria is in another column. Eg a table with two columns A1:A10, B1:B10 and criteria in D1:D3. I want to sum the values in B that matches the criteria in A. Thanks
Thanks very much!!!
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?
Good day.
I would like to ask what to do if my lookup value inside sumproduct is a result of another excel function.
I initially tried to put "value" before the lookup value but it only works for number, unfortunately I have look up values that is a combination of letters and number.
Awesome!!!
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!
Simply Amazing :)
Thanks for the Awesom video Leila Gharani....
You're very welcome.
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.
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....
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?
awesome and very helpful, thanks
Good go ,please keep up