Great teaching style! Wonderful format with great examples You break formula's down and explain them step by step. Well done thanks. Great learning video, series thanks
Impressive how Leila saved my life so many times!!! hehehe Everytime when I have a problem I will check if my Guardian angel can help me.... most of the times she can! 😇🥰 Thank you!!
You miss, have saved me from my 5 hour agony from figuring this out. Turns out I was doing columns THEN rows instead of rows THEN columns. Thank you very much!
Hi Leila, I stumbled upon your videos by random and love that your videos are high quality and very elegantly created compared to other Excel videos. I hope you continue to make great videos on Excel with emphasis on functions that'll be helpful for a marketing or finance person :)
Hi Leila, can you do a video to show how to do index match match with a table where the row headers and column headers are numbers? So by inputting a number within the range of row headers, and another number within the range of column headers, it would return a number from the table. Where the numbers are not exactly matching the headers, the selection would look towards the next higher number.
Hi Leila, I have really enjoyed and improved my Excel use thanks to your great work! One area I still struggle with is when data that is two dimensional (say rates by city and state) but the data has columns for state, city and rate (i.e., not in a state by city table). I've always had to create multiple ranges of look ups by, in this case, state. Is there a more efficient way?
You're very welcome. I'm glad you like the explanation! It's difficult to get the hang of it. It took me a while....but once it clicks, it's just so much more flexible than Vlookup.....
Immense respect! As a teacher, you foresee what kind of problems a pupil could encounter and give a solution for that! Simply amazing! I have a question though, how could we apply index & match to an array (taking data from an array and putting answers on an array) using CSE! I could work it around by using additional index within match but otherwise with CSE it ceases to be dynamic on the match argument!
Thank you for making this quick and easy. This video was so good, I wish I found this years ago! YOU are good at explaining the logic and steps. You've got a new excel formula nerd fan!
Hi mam I am enjoying all of your videos. I m quit glade to found your channel because your talking style is quit pretty and clear. Mam how can I have this complete workbook you are showing in the video because I have downloaded this workbook I found jst two sheets in it. Please email me this complete workbook thanks
Hi Leila, Just like SUMPRODUCT, the last way of accomplishing this task you mentioned is beyond my current grasping capability. I am wondering if you could also add the INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(1,(I20=C20:F20)*(I21=C21:F21),0)) as a viable forth method of accomplishing this. This would work because INDEX is forcing it to become array. If it was only match by itself then you would have to make it an array (which in excel is ctrl+shift+enter and in google sheets is encapsulating the formula within ARRAYFORMULA()). Hope this helps.
Another fab video. Looks interested but can be complicated for many people. I have been working on complex INDEX MATCH situations and have mastered it through a lot of real life cases. during my training sessions. Your videos are great to learn from, especially your techniques. Thanks.
Two minutes in and I had the answer to the question that had been bugging me for hours! I had transposed the Index and Match ranges so it was returning and N/A. So clear. Thanks. 👍🏻😎
Here's some more VBA fun---Code has the drop down list at K22 Sub KPI() Dim FLrange As Range, Cell As Range, ListRange As Range Set FLrange = Range("B22", Range("B22").End(xlDown)) 'Set ListRange = Range("K22") Range("L22").ClearContents With Sheet2 For Each Cell In FLrange If Cell.Value = Range("K22").Value And Range("L20") = "Actual" And Range("L21").Value = "Revenue" Then Cell.Offset(0, 1).Copy Range("L22").Select Range("L22").PasteSpecial End If If Cell.Value = Range("K22").Value And Range("L20") = "Actual" And Range("L21").Value = "Profit" Then Cell.Offset(0, 2).Copy Range("L22").Select Range("L22").PasteSpecial End If If Cell.Value = Range("K22").Value And Range("L20") = "Budget" And Range("L21").Value = "Revenue" Then Cell.Offset(0, 3).Copy Range("L22").Select Range("L22").PasteSpecial End If If Cell.Value = Range("K22").Value And Range("L20") = "Budget" And Range("L21").Value = "Profit" Then Cell.Offset(0, 4).Copy Range("L22").Select Range("L22").PasteSpecial End If Next
Hi Leila, I tried Advanced Index Match following your video. However, I'm facing one issue. In the Complex Example, when I used this equation: "INDEX(C213:F222,MATCH(H214,B213:B222,0),MATCH(I212&I213,C210:F210,0))" I got my answers correctly according to the equation but it throws an error "#N/A" while I shift to "Actual Profit/Revenue". The same does not happen in "Budget Profit/Revenue". Why is that so? Please help. Looking forward to your reply.
Hi Leila wont the AND function work as well on the 2nd match? eg using your sheet =Index(c22:f31,match(h22,B22:B31,0),and(match(i20,c20:f20),match(i21,f20:f21,0)))
I do it like this and have no problems (I re-did you table starting from column L): =INDEX(C22:F31,MATCH(L22,B22:B31,0),MATCH(M20&M21,C20:F20&C21:F21,0))
That extra INDEX within the MATCH to avoid CSE in the last formula is apparently no longer necessary since now C20:F20&C21:F21 = INDEX(C20:F20&C21:F21,0). I guess this is a new Excel 360 feature. Anyway, remarkable video. Thankyou much
just curious: seems like I can use below formula that did not apply "CSE" to achieve the same result without error, and the returned value looks correct. can anyone help? '=INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F20&C21:F21,0))
Leila, just looked at your video... would te same work if column H and I were on a different sheet? I think this MATCH gives strange things for me: MATCH(ROW(Sheet1!$A$3:$A$28);Sheet1!$A$3:$A$28))... Or could it be the INDEX function cannot look at other sheets?
Another option, for the second match use: 'match(1, (row 1=ref)*(row 2 = ref 2),0) Explanation: if row 1 = ref returns true/1 and row 2 = ref 2 returns true, 1*1 = 1 / a match!
Hi Leila. Thank you for the awesome video. Can you please show how to combine sumproduct and index? A B C D 1 2018-2019 2 Account # Actual January February 3 6502 $20,747.39 $235.00 $654.00 4 6502 $1,480.80 $653.00 $135.00 5 7502 $5,645.00 $545.00 $654.00 6 7806 $64,554.00 $365.00 $496.00 I have a spreadsheet with too many columns and lines. I need a formula to summarize amounts that meet criteria of "2018-2019 and Actual, and 6502" In this example, B3 + B4 = $22,228.19. Thanks to you I know how to use sumproduct and index, but cannot figure out how to combine them. =SUMPRODUCT(($A$3:$A$6=6502)*(B3:B6)) =INDEX(A3:D6,MATCH(6502,A3:A6,0),MATCH(B1&B2,INDEX(A1:D1&A2:D2,0),0)) This formula returns first number only $20,747.39 Thank you.
Good teaching and Knowledge Leila Mam. Namo Namo Ji Shankara song by me on my youtube channel in memory of the great talented actor Sushant Singh Rajput 🙏🙏
Hi Leila, You may already know this one but: Index Match to return arrays, simple but can be slow as you return more columns, right? =INDEX([Target Table Column&Column2&Column3...],MATCH([@Key],[Target Table Key Column],0)) CSE This was running me 20+ minutes. Now I use this: =CONCAT(OFFSET(INDEX([Target Table Column1],MATCH([@Key],[Target Table Key Column],0)),0,0,,COUNTA([Target Table Headers or header row]))) 🙂 🙂 🙂 🙂 🙃🙃 🙂 🙃🙃🙃 Note the empty commas for offset, as I learned from your videos
The first two tabs on the practice file are titled "Index Match Example 1" and "Index Match Example 2" - these are not covered in this video. There seems to be multiple videos which correspond to this one practice file. Where are the videos which cover the first two tabs? Would it be possible to show what videos apply to each practice tab? Thanks so much!
Yes,awesome index and match multiple function,the most beautiful and gorgeous Excel instructor at all.Send you the song===Just to see her,dreaming of you,Same ole love@@@
Hello, question. What if i use index match inside the countifs function to count the word "MISS" with the same criteria. It's a struggle for me. Thank you! Please reply.
Also what happends when my Array is a whole column for example A:A, but the information is between 2k and 3k rows long, what´s better to keep it cool, to directly put the A1:A3000 or just leave it at A:A and let it figure it out,--- i´m using your 3rd formula with 3 criteria, on 3 different columns, so i´m using index with the whole columns, which is better?
Hi Leila I have a question for the Index and Match What is the array formula for "Less than" in the ()*()*(),1) If one of the conditions when need to have "Less than" rather than exact match?! Eric
Good day. I have a different problem. Lets say (in your example), that some apps could appear in different divisions with different revenues, etc. ie. Fightrr can be both a Game and a Utility. How can I get both revenues out of there. I usually get the first coincidence from both vlookup or index-match.
Hello, I am a follower of your lessons and your lessons are beautiful. I wish the translation into Arabic would be combined with the lessons. Thank you very much
Where can i find tutorial how to include C22-C31numbers, but exclude all other numbers, except those that are at the same row as the 'Game' between the A22-A31 (If they would be spread randomly at that range), and then get the sum of the numbers gathered (C22-C25 that match the numbers with the word 'Game') and print that to a certain cell? What kind of function i need to do that?
Find price where expiy = expiry and code = code Pls solve this for me if you look at the data 21 feb expiry and 28 feb expiry has same code and the price is different
Can someone explain how could the second INDEX function at 8:40 work that way? Because as I understand, the basic functionality of INDEX is to look up in a range and return a specific value. But in this case, it seems to perform a very different task here
Forgive my ignorance but couldn’t you put the words “Actual Revenue “ or “Actual Profit” etc in one cell by using the “wrap text” option in one cell so the formula can focus on the single cell?
It seems that with the new calc engine in Office 2021 / Microsoft 365, no need to Ctrl + Shift + Enter ... I did it without using that and it works fine.
Hi Leila/Community! Is there a way to sum an array of data based on the column that the data falls under? For example, I have columns with date headers (all EOM dates) and I only want to sum the data in the columns for column dates after todays date. My thought process is "if column date>Today return sum of column" but I can't figure out a formula to do that function.
Hi, new here and subscribed I don’t know if you reply to these comments for further assistance but I am having problem doing this exact formula using double headers in row instead of column in your video and I am not getting the right value. I can get it to work with ctrl shift enter but without, it won’t work. Can you please help? Thank you I’m advance
So my whole table works except when i select Actual Revenue (other options work), ive checked every single step of my forumla bar and the data i entered. Ive no idea why its not working. Its the same problem across all 3 solutions. Pls help?
My array function seems to be working despite not using the Ctrl+Shift+Enter method. Is this due to an update, leaving this method redundant? If anyone knows why this is happening please let me know! thank you.
Dear Leila, Can you please help? i tried everything, i searched google but no joy. I want to select the Nth row ie D5:P5 where N is a random number in column A
Dear Mam, Please help me i need a formula...if my data is in a Colum which is in different cell ..and same data value is down side of columns in different row.. which is also in different cell... i need sum of value which three or four cell cell data in a cell...... for example.... COLOUM DATA 1 2 3 16 18 20 SUM VALUE 50 500 120 240 240 240
COLOUM DATA SUM VALUE 1-2-3 670 2-3-16 860 3-18-20 600 20-3-2 ?
Hello thank you for your videos. Do you have an example when we can combined index and secuence function? In order to find an specific info in a range and do something with them. For example every two weeks to sum some values etc?
It is a great video and great way to explain things. Just a point, when I try the "array solution with CSE" it works for me even when I don't use ctrl+shift & enter. any idea why?
GREAT video - I tried to use the array CSE with a table that has multple row criteria instead of column criteria. I keep getting an error. Is it not possible to use your instructions for this scenario?
Is there a way to create a dependent drop down from data in two rows that vary only in one category? Example: I have 2 lots (Lot #1, and Lot #2) of Product A. How can I create a drop down showing only the 2 lots after choosing product A first?
I'm still not able to return the value I'm looking for in a 3 way look up. Any chance I can send you the info for further direction? appreciate any help you can provide
Great video series about VLOOKUP, INDEX and MATCH for complex lookups. Love your teaching style and details Leila 💪 Questions: Is it possible to create a new Excel function including INDEX/MATCH to ease the data selection form the search table furthermore? For big data tables, is there a noticeable difference in search speed, memory usage? Which one would be the better pick there?
what if you have data from difference excels that need to consolidate into a single excel file? Ex: budget and actual data from 5 companies and different months, how to update the value using the formula without manually changing the reference link in the formula? Thanks!
What about, when we have multiple repeated values in rows, Eg: the same product sold several times in a row by different countries, and you want to find the product name using countries?
Can somebody help me to write a code in VBA? When my combobox1 and combobox2 changes values i want in other 14 textboxes to be displayed the value that is matching the row of combobox1 and combobox2? I know how to make it when just one combobox changes but not when both do since it depends on two comboboxes?
Hi, I use the index and sumproduct formula and it returns all the index options and creates a #spill error. How do I have it return specific text? For example not found or not in array. Will an if function help. Thank you for any help you can provide
Mam, i have a doubt...could you please help me to write formula for census report .. i want to display the rows which satisfy the names of students who are within particular age range, and satisfy other criteria like category, sex, school name etc..please..
Hello in case I need to match data from two different tables and with 2 conditions, how does it work? I have a table with 4 columns: Date of issue, Invoice number, client and total amount and on the other hand I have another table with 4 columns: Date of payment, invoice number, client, and total amount. I neet to match data from these 2 table in order to know which invoices have not been paid yet, so bearing in mind that the same client has paid serveral invoices with the same total amount (but different invoice number) how to do it? Thank you very much.
Grab the file I used in the video from here 👉 pages.xelplus.com/index-match-advanced-file
I am watching on my phone.
I have trouble seeing the equations.
Awesome INDEX video!!! It a great solution that so many business people encounter!
Thanks Mike. Very true! I get this question all the time....
Leila & Mike, discovering both your channels has been an absolute revelation for me!
You Do Some Awesome explanations too Man and I love how you speak :D
2 of my favorite excel gurus 😃
Two genius at one place... 😘😘
That extra index within the match to avoid CSE is genius, thank you,... Really learning from you channel! Keep it up.
That extra index within the match to avoid CSE is apparently no longer necessary with EXCEL 360 since C20:F20&C21:F21 = INDEX(C20:F20&C21:F21,0)
The Excel Goddess! No matter what crazy scenario in which I find myself, Leila Gharani has the answer!
😘
I finally get it!! After all these years getting frustrated with lookups this is going to be so helpful. Thank you for your amazing videos
YES! YES! I'm very happy to hear that :)
who were those 45 idiots to dare disliking this sweet girl's video? :(
She is the Excel Queen. I learnt a lot from her.
"Wow, this is such a cool formula"... That sounds like me looking at other people's spreadsheets.
lol
lmao so true
Great teaching style! Wonderful format with great examples You break formula's down and explain them step by step. Well done thanks. Great learning video, series thanks
You're very welcome Stephen. Glad to hear that.
These are the best tutorials I've found anywhere!!!
It seems like they have fixed the need for ctrl+shift+entre in office 365? i.e. you can now press just entre?
marry me , i have been killing my self with different solutions and getting error messages and i find this.... just marry me
Impressive how Leila saved my life so many times!!! hehehe
Everytime when I have a problem I will check if my Guardian angel can help me.... most of the times she can! 😇🥰
Thank you!!
Mam I'm really impressed with ur way of explaining n thanks a lot
You're very welcome Teja.
You miss, have saved me from my 5 hour agony from figuring this out. Turns out I was doing columns THEN rows instead of rows THEN columns. Thank you very much!
Queen of Excel, Leila Thank you for this video
Hi Leila, I stumbled upon your videos by random and love that your videos are high quality and very elegantly created compared to other Excel videos. I hope you continue to make great videos on Excel with emphasis on functions that'll be helpful for a marketing or finance person :)
Happy to hear that :) Sure - will do my best. Let me know if there is anything specific you're looking for and I'll gladly add to my list.
Hi Leila, can you do a video to show how to do index match match with a table where the row headers and column headers are numbers? So by inputting a number within the range of row headers, and another number within the range of column headers, it would return a number from the table. Where the numbers are not exactly matching the headers, the selection would look towards the next higher number.
Hi Leila, I have really enjoyed and improved my Excel use thanks to your great work! One area I still struggle with is when data that is two dimensional (say rates by city and state) but the data has columns for state, city and rate (i.e., not in a state by city table). I've always had to create multiple ranges of look ups by, in this case, state. Is there a more efficient way?
THANK YOU.... I really like how well you explain things.. I will try to use this instead of VLookup to learn better.
You're very welcome. I'm glad you like the explanation! It's difficult to get the hang of it. It took me a while....but once it clicks, it's just so much more flexible than Vlookup.....
This is the reason why I loved index match function.
Immense respect! As a teacher, you foresee what kind of problems a pupil could encounter and give a solution for that! Simply amazing!
I have a question though, how could we apply index & match to an array (taking data from an array and putting answers on an array) using CSE! I could work it around by using additional index within match but otherwise with CSE it ceases to be dynamic on the match argument!
Wow!!! scratched my head out for an entire day, until I found this genius solution. You're the best
Glad it helped!
You are great Leila... Awesome I'm a big fan of your teachings...
Hi, Leila! Great videos! I have Office 365 and method 2 works without CSE as well...
Thank you for making this quick and easy. This video was so good, I wish I found this years ago! YOU are good at explaining the logic and steps. You've got a new excel formula nerd fan!
I followed step-by-step and it works... and I understand the thinking behind it (that matters a lot!).
So true! I'm glad the video was helpful.
i love the way you teach
I'm glad to hear that.
super.. am going to user index & match hereafter, so many years i was doing things in lookups. Thank you.. for such a learning videos..
Hi mam
I am enjoying all of your videos. I m quit glade to found your channel because your talking style is quit pretty and clear.
Mam how can I have this complete workbook you are showing in the video because I have downloaded this workbook I found jst two sheets in it. Please email me this complete workbook thanks
Hi Leila,
Just like SUMPRODUCT, the last way of accomplishing this task you mentioned is beyond my current grasping capability.
I am wondering if you could also add the INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(1,(I20=C20:F20)*(I21=C21:F21),0)) as a viable forth method of accomplishing this. This would work because INDEX is forcing it to become array. If it was only match by itself then you would have to make it an array (which in excel is ctrl+shift+enter and in google sheets is encapsulating the formula within ARRAYFORMULA()).
Hope this helps.
Index match..... Crystal clear 😊 Thanks!!!!
Your content is an absolute LIFESAVER. Thank you so much for all the videos, your content is so easy to understand!
Another fab video. Looks interested but can be complicated for many people. I have been working on complex INDEX MATCH situations and have mastered it through a lot of real life cases. during my training sessions. Your videos are great to learn from, especially your techniques. Thanks.
You're welcome Awesh. I agree - it does some practice to get the hang of these....
Two minutes in and I had the answer to the question that had been bugging me for hours! I had transposed the Index and Match ranges so it was returning and N/A. So clear. Thanks. 👍🏻😎
Here's some more VBA fun---Code has the drop down list at K22
Sub KPI()
Dim FLrange As Range, Cell As Range, ListRange As Range
Set FLrange = Range("B22", Range("B22").End(xlDown))
'Set ListRange = Range("K22")
Range("L22").ClearContents
With Sheet2
For Each Cell In FLrange
If Cell.Value = Range("K22").Value And Range("L20") = "Actual" And Range("L21").Value = "Revenue" Then
Cell.Offset(0, 1).Copy
Range("L22").Select
Range("L22").PasteSpecial
End If
If Cell.Value = Range("K22").Value And Range("L20") = "Actual" And Range("L21").Value = "Profit" Then
Cell.Offset(0, 2).Copy
Range("L22").Select
Range("L22").PasteSpecial
End If
If Cell.Value = Range("K22").Value And Range("L20") = "Budget" And Range("L21").Value = "Revenue" Then
Cell.Offset(0, 3).Copy
Range("L22").Select
Range("L22").PasteSpecial
End If
If Cell.Value = Range("K22").Value And Range("L20") = "Budget" And Range("L21").Value = "Profit" Then
Cell.Offset(0, 4).Copy
Range("L22").Select
Range("L22").PasteSpecial
End If
Next
End With
End Sub
What if
A1 is Paul Levine
B1 is Paul Levine Morrison
I want this to show as MATCH as both are correct but b1 is just more value in it
great job! And it's the same way to use xlookup, like this: XLOOKUP(H22,B22:B31,XLOOKUP(I20&I21,C19:F19,C22:F31))
=INDEX(C22:F21,MATCH(H22,B22:B31,0),MATCH(I20,C20:F20,0)+MATCH(I21,C21:F21,0)-1) would also do it, without the helper columns or array formulas.
Hi Leila,
I tried Advanced Index Match following your video. However, I'm facing one issue. In the Complex Example, when I used this equation:
"INDEX(C213:F222,MATCH(H214,B213:B222,0),MATCH(I212&I213,C210:F210,0))"
I got my answers correctly according to the equation but it throws an error "#N/A" while I shift to "Actual Profit/Revenue". The same does not happen in "Budget Profit/Revenue". Why is that so? Please help.
Looking forward to your reply.
Hi Leila wont the AND function work as well on the 2nd match? eg using your sheet =Index(c22:f31,match(h22,B22:B31,0),and(match(i20,c20:f20),match(i21,f20:f21,0)))
I do it like this and have no problems (I re-did you table starting from column L): =INDEX(C22:F31,MATCH(L22,B22:B31,0),MATCH(M20&M21,C20:F20&C21:F21,0))
That extra INDEX within the MATCH to avoid CSE in the last formula is apparently no longer necessary since now C20:F20&C21:F21 = INDEX(C20:F20&C21:F21,0). I guess this is a new Excel 360 feature. Anyway, remarkable video. Thankyou much
just curious: seems like I can use below formula that did not apply "CSE" to achieve the same result without error, and the returned value looks correct. can anyone help?
'=INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F20&C21:F21,0))
Leila, just looked at your video... would te same work if column H and I were on a different sheet? I think this MATCH gives strange things for me: MATCH(ROW(Sheet1!$A$3:$A$28);Sheet1!$A$3:$A$28))... Or could it be the INDEX function cannot look at other sheets?
In Office365, use ENTER instead of CSE for "=INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F20&C21:F21,0))" now.
Could you please make a video with the combination of VLOOKUP, HLOOKUP & MATCH...
Thank you for this helpful video! The way you explain excel is so much easier to comprehend than other instructors.
Another option, for the second match use: 'match(1, (row 1=ref)*(row 2 = ref 2),0)
Explanation: if row 1 = ref returns true/1 and row 2 = ref 2 returns true, 1*1 = 1 / a match!
Hi Leila. Thank you for the awesome video. Can you please show how to combine sumproduct and index?
A B C D
1 2018-2019
2 Account # Actual January February
3 6502 $20,747.39 $235.00 $654.00
4 6502 $1,480.80 $653.00 $135.00
5 7502 $5,645.00 $545.00 $654.00
6 7806 $64,554.00 $365.00 $496.00
I have a spreadsheet with too many columns and lines. I need a formula to summarize amounts that meet criteria of "2018-2019 and Actual, and 6502"
In this example, B3 + B4 = $22,228.19.
Thanks to you I know how to use sumproduct and index, but cannot figure out how to combine them.
=SUMPRODUCT(($A$3:$A$6=6502)*(B3:B6))
=INDEX(A3:D6,MATCH(6502,A3:A6,0),MATCH(B1&B2,INDEX(A1:D1&A2:D2,0),0)) This formula returns first number only $20,747.39
Thank you.
Good teaching and Knowledge Leila Mam. Namo Namo Ji Shankara song by me on my youtube channel in memory of the great talented actor Sushant Singh Rajput 🙏🙏
Hi Leila, You may already know this one but:
Index Match to return arrays, simple but can be slow as you return more columns, right?
=INDEX([Target Table Column&Column2&Column3...],MATCH([@Key],[Target Table Key Column],0)) CSE
This was running me 20+ minutes.
Now I use this:
=CONCAT(OFFSET(INDEX([Target Table Column1],MATCH([@Key],[Target Table Key Column],0)),0,0,,COUNTA([Target Table Headers or header row])))
🙂 🙂 🙂 🙂 🙃🙃 🙂 🙃🙃🙃
Note the empty commas for offset, as I learned from your videos
The first two tabs on the practice file are titled "Index Match Example 1" and "Index Match Example 2" - these are not covered in this video. There seems to be multiple videos which correspond to this one practice file. Where are the videos which cover the first two tabs?
Would it be possible to show what videos apply to each practice tab? Thanks so much!
Yes,awesome index and match multiple function,the most beautiful and gorgeous Excel instructor at all.Send you the song===Just to see her,dreaming of you,Same ole love@@@
Your clarity in excel is amazing. The way you have explained it here is unmatchable. Keep it up.
Hello, question. What if i use index match inside the countifs function to count the word "MISS" with the same criteria.
It's a struggle for me.
Thank you! Please reply.
Also what happends when my Array is a whole column for example A:A, but the information is between 2k and 3k rows long, what´s better to keep it cool, to directly put the A1:A3000 or just leave it at A:A and let it figure it out,--- i´m using your 3rd formula with 3 criteria, on 3 different columns, so i´m using index with the whole columns, which is better?
Omg, i have two days looking for a formula that can find my result and i finally find this video.
It helps me a lot , thank you very much😊
Glad it helped!
Hi Leila
I have a question for the Index and Match
What is the array formula for "Less than" in the ()*()*(),1)
If one of the conditions when need to have "Less than" rather than exact match?!
Eric
Good day. I have a different problem. Lets say (in your example), that some apps could appear in different divisions with different revenues, etc. ie. Fightrr can be both a Game and a Utility. How can I get both revenues out of there. I usually get the first coincidence from both vlookup or index-match.
Hello, I am a follower of your lessons and your lessons are beautiful. I wish the translation into Arabic would be combined with the lessons. Thank you very much
My mind is blown away by the beauty and simplicity of these insanely complex formulas. Your method is very helpful. Thank you!
Where can i find tutorial how to include C22-C31numbers, but exclude all other numbers, except those that are at the same row as the 'Game' between the A22-A31 (If they would be spread randomly at that range), and then get the sum of the numbers gathered (C22-C25 that match the numbers with the word 'Game') and print that to a certain cell? What kind of function i need to do that?
Leila, using SUMPRODUCT in this case it would make it much easier, am I right?
Date Expiry Code Price expiry Code Price
28-Feb-19 14-Feb-19 10950 71.10 14-02-2019 10950 ?
28-Feb-19 14-Feb-19 10950 30.00 14-02-2019 10950 ?
28-Feb-19 14-Feb-19 10950 16.25 14-02-2019 10950 ?
28-Feb-19 14-Feb-19 10950 0.45 14-02-2019 10950 ?
28-Feb-19 21-Feb-19 10800 73.00 21-02-2019 10800 ?
07-Mar-19 21-Feb-19 10800 50.00 21-02-2019 10800 ?
07-Mar-19 21-Feb-19 10800 17.70 21-02-2019 10800 ?
07-Mar-19 28-Feb-19 10800 58.30 28-Feb-19 10800 ?
21-Feb-19 28-Feb-19 10800 62.30 28-Feb-19 10800 ?
22-Feb-19 28-Feb-19 10800 181.25 28-Feb-19 10800 ?
25-Feb-19 28-Feb-19 10800 250.00 28-Feb-19 10800 ?
Find price where expiy = expiry and code = code
Pls solve this for me
if you look at the data 21 feb expiry and 28 feb expiry has same code and the price is different
Hi,,I try to use index and match for multiple criteria for less or greater match value, but it does not work..what’s wrong?
Can someone explain how could the second INDEX function at 8:40 work that way? Because as I understand, the basic functionality of INDEX is to look up in a range and return a specific value. But in this case, it seems to perform a very different task here
For some reasons in the first formula, the Budget/ Profit selection gives N/A values for any apps selected!!
Forgive my ignorance but couldn’t you put the words “Actual Revenue “ or “Actual Profit” etc in one cell by using the “wrap text” option in one cell so the formula can focus on the single cell?
It seems that with the new calc engine in Office 2021 / Microsoft 365, no need to Ctrl + Shift + Enter ... I did it without using that and it works fine.
Hi Leila/Community!
Is there a way to sum an array of data based on the column that the data falls under? For example, I have columns with date headers (all EOM dates) and I only want to sum the data in the columns for column dates after todays date. My thought process is "if column date>Today return sum of column" but I can't figure out a formula to do that function.
Hi, new here and subscribed
I don’t know if you reply to these comments for further assistance but I am having problem doing this exact formula using double headers in row instead of column in your video and I am not getting the right value. I can get it to work with ctrl shift enter but without, it won’t work. Can you please help? Thank you I’m advance
So my whole table works except when i select Actual Revenue (other options work), ive checked every single step of my forumla bar and the data i entered. Ive no idea why its not working. Its the same problem across all 3 solutions. Pls help?
My array function seems to be working despite not using the Ctrl+Shift+Enter method. Is this due to an update, leaving this method redundant? If anyone knows why this is happening please let me know! thank you.
Dear Leila, Can you please help? i tried everything, i searched google but no joy. I want to select the Nth row ie D5:P5 where N is a random number in column A
Dear Mam,
Please help me i need a formula...if my data is in a Colum which is in different cell ..and same data value is down side of columns in different row.. which is also in different cell...
i need sum of value which three or four cell cell data in a cell......
for example....
COLOUM DATA 1 2 3 16 18 20
SUM VALUE 50 500 120 240 240 240
COLOUM DATA SUM VALUE
1-2-3 670
2-3-16 860
3-18-20 600
20-3-2 ?
Hello thank you for your videos. Do you have an example when we can combined index and secuence function? In order to find an specific info in a range and do something with them. For example every two weeks to sum some values etc?
It is a great video and great way to explain things. Just a point, when I try the "array solution with CSE" it works for me even when I don't use ctrl+shift & enter. any idea why?
GREAT video - I tried to use the array CSE with a table that has multple row criteria instead of column criteria. I keep getting an error. Is it not possible to use your instructions for this scenario?
Is there a way to create a dependent drop down from data in two rows that vary only in one category? Example: I have 2 lots (Lot #1, and Lot #2) of Product A. How can I create a drop down showing only the 2 lots after choosing product A first?
I'm still not able to return the value I'm looking for in a 3 way look up. Any chance I can send you the info for further direction? appreciate any help you can provide
Great video series about VLOOKUP, INDEX and MATCH for complex lookups. Love your teaching style and details Leila 💪
Questions: Is it possible to create a new Excel function including INDEX/MATCH to ease the data selection form the search table furthermore?
For big data tables, is there a noticeable difference in search speed, memory usage? Which one would be the better pick there?
Thanks , I was searching this type video of multiple criteria in index match.. many thanks Mam
Very clear & understandable. Well done, Cheers
Can you please type the formulas in the description box? It was challenging to see in the video, and I had to keep pausing.
what if you have data from difference excels that need to consolidate into a single excel file? Ex: budget and actual data from 5 companies and different months, how to update the value using the formula without manually changing the reference link in the formula? Thanks!
What about, when we have multiple repeated values in rows, Eg: the same product sold several times in a row by different countries, and you want to find the product name using countries?
Can somebody help me to write a code in VBA? When my combobox1 and combobox2 changes values i want in other 14 textboxes to be displayed the value that is matching the row of combobox1 and combobox2? I know how to make it when just one combobox changes but not when both do since it depends on two comboboxes?
Hello.. how do you create cells I21 and I22? I understand creating a list using data validation, but I'm not able to recreate it exactly as shown here
Hi, I use the index and sumproduct formula and it returns all the index options and creates a #spill error. How do I have it return specific text? For example not found or not in array. Will an if function help. Thank you for any help you can provide
Mam, i have a doubt...could you please help me to write formula for census report .. i want to display the rows which satisfy the names of students who are within particular age range, and satisfy other criteria like category, sex, school name etc..please..
Hello in case I need to match data from two different tables and with 2 conditions, how does it work? I have a table with 4 columns: Date of issue, Invoice number, client and total amount and on the other hand I have another table with 4 columns: Date of payment, invoice number, client, and total amount. I neet to match data from these 2 table in order to know which invoices have not been paid yet, so bearing in mind that the same client has paid serveral invoices with the same total amount (but different invoice number) how to do it? Thank you very much.
seems excel 360 can handle method 2 now w/o hitting Ctrl+alt+Enter, method 3 that additional Index is not needed any more.
Thank you for sharing your tips 😇😇😇
This is not exactly multiple criteria search. Multiple criteria should find the intersection of two rows or columns.
Why can't we use textjoin or concat to combine the information in different cells
Your formula to good, same we need, other show short formula, we need big one..... Thanks 😊
You are great.....really I mean it...your session helped me solving a typical problem..
it's always confusing to use index&match but dis time the way u dealt with really helpful
Glad to hear my explanation was clear. Thanks Jacky.
Can I index multiple columns sitting on different tabs in the same worksheet??
Will these methods work in Microsoft 365?