I have watched different videos to learn and understand index match function. Finally understand. Thanks a lot for teaching in easy way. Keep up the good work.
Good morning, Sir. Thank you for your hard work. I personally have learned a lot from your videos on TH-cam here. It's 2:14 AM here in Sierra Leone local time. I Strongly want to become a Business data Analyst. Once again thank you very much and God Bless you
I watched multiple videos on TH-cam for however, your video was super simple and well-explained. Thank you so much for creating such videos and educating us.
Great tutorial, very simple and easy to follow. One small comment for the bonus example, I believe a “sumproduct” function with 3 conditions will be a better option to use
Amazing video, I observe one thing that is at the Advanced IndexMatch, after typing the function we need to pressing Ctrl + Shift + Enter instead of just enter🙂
Thank you, this was very helpful. I am still a little confused about the $ to fix columns and rows, but other than that I was able to create my little project :)
Hi Kenji, what a great video to upgrade my excel skill. Could you please explain what is the pros & cons using function of vlookup, offset and index match in linking a huge database? Thanks
GREAT VIDEO!!! For #4 Advanced, I just put in a third "MATCH" function and it seems to be working. Is there a reason you don't prefer this? =INDEX(C10:K29,MATCH(C3,B10:B29,0),MATCH(C4,C8:K8,0),MATCH(C5,C9:K9,0))
The xlookup function is very powerful which addresses most of the limitations the vlookup/hlookup had. Can u pls tell me how Index Match is still better, I had the impression xlookup also addresses Index Match
Greetings, That was an interesting question. I've reviewed your question and here is my thought. The Limitation of XLOOKUP is, it cannot handle two dimensional data while searching for the values unlike the index and match does. Meaning in XlOOKUP only one specific column can be chosen where our search search criteria exists. Hope that answers the question.
Hi Kenji, do you know how to use the index match formula with 2 arguments but when 1 argument is that the reference cell needs to contain words from the array, but the reference cell can contain those words and other words as well?
Question, when you do index match's does it matter which critera you match first i.e. for the dynamic index match example where your first match is based on revenue,GP and Net income and the 2nd match is based on the year (2020 and 2022), could I switch the matches around i.e. the first match being based on the years (2020 & 2022) and the second being on revenue, GP and Net Income?
Is it possible to do a sumifs formula within the the entire index? For example, say there were two "Spain" in the left column with the value of 7,000 and 4,000 for March 2021. Is there a way to build in the sumifs into the entire index so that 11,000 is the answer?
Hey Kenji, thanks for this great video! I was wondering if you could tell us how to lookup a number if we have a minimum and maximum range given. For example, if any zip code's between 99501 & 99950 it'd be under the state of Alaska! How can I look up the state if I were given a bunch of postal codes.
Great one, Kenji! index/match competes with xlookup. is level 4 possible for xlookup? Supernice Tipp about the double criteria! what are differences between xlookup and index/match approach?
Hi Bro! Is it possible to add 3 in the bonues Scenario ? =SUM(FILTER(FILTER(B20:M22,A20:A22=A15),B19:M19=C15,)) please let me know if is possible thank you
Hi Kenji, awesome tutorial!!!! I need some help if I may ask you with extracting the data from two tables which contains date, client, amount. The data set is not consistent because the client invoice amount is different to the billable amount. Which formula will I use to get the invoice amount specific to a client against the billable amount?
some of us dont use the newer versions of excel and hence don't have access to functions like filter. So how do we go about the bonus part? I think SUMIF should work but if not then how do we do it?
Hey Kenji, any chance you can create an SQL Course? So many finance/accounting analyst roles are now asking candidates to know SQL. I would totally buy an SQL course if you made one. If not, i would love to hear from you if there is an SQL course you recommend
Thank you! The SUMIFS unfortunately doesn't work. That's because the criteria ranges and the sum range don't have the same number of rows and columns (feel free to try it and let me know if otherwise though)
I am interested in the DYNAMIC DATASET MATCH problem. I have a similar dataset which requires the same formula construction. The key difference is that I am working with TABLES. Therefore, the issue I face is that the first LOOKUP_ARRAY keeps moving as i drag my formula horizontally. There does not seem to be anyway to 'lock' this array as in the case with $. Any tips/ solutions?
Hi Kenji. I would like to ask a question in excel which i was not able to solve in an interview where i had to pull sales value of a country in a category and another category for different years. example: sales value of coffee in US when sales value and volume are given. so like we had 3 columns to look from and one row of year. how do we solve this. could you please help
What if there’s multiple results? For example clients and what company they work for. Our Index I think would be the clients names, if we’re sorting by company.. match would be companies name.. but only one clients name comes up even if I drag out the formula. How can I get all the clients names that work at that company?
For the advanced "Index Match", I do get an error which says"#Value!" Is there any error that I do? the formula that I enter in the same sheet of yours. =INDEX(C10:K29,MATCH(C3,B10:B29,0),MATCH(C4&C5,C8:K8&C9:K9,0))
Hi, I have got a small doubt. In the last section, you've given a filtered array in the FILTER() to get for January month. However, the filtered array doesn't have any header for the months. How's it giving results then?
If you look at the 2nd argument for the outer filter function it's referencing C9:J9 = C4, that's where it is getting it from, so it's not the filtered array but rather the original one it is looking up for the month.... at least I think so.. also when you filter an array normally it doesn't get rid of the headers
Thank you! Unfortunately, I don't have a google sheets course but it's something to consider. Can I ask why you use google sheets? is it because your company uses it, because it's free, or something else?
3 criteria formula is not working in excel 2007 ( 3 criteria) where you use & Will this not work or i am doing something wrong because i match my formula was exactly same with you...
great question! Unfortunately, you can't because the index function has 2 key arguments which are the row and column. So if you try to feed the row argument both a row and a column using the ampersand it doesn't work.
👉 See all our courses here! www.careerprinciples.com/courses
as a professional what would you comment on CFA as a course?
Hello can you make a progress report or class record of students in different levels using this index matching
Thanks for all your content! It's very clear and makes Excel really enjoyable. I am a Finance professional and I really appreciate your work.
more explanation on how many times f4 is pressed and when
Thanks man, it seems like I got a refresher but in easier to understand plus with practice file. You're a star!
The most understandable explanations of EXCEL formula use on TH-cam. Thanks Kenji!
Glad it was helpful!
I'm brazilian and this video is AMAZING, the "Advanced Index" help me a lot!! Bro you explain the best so far! Thanks Kenji.
Happy to help!
I have a job interview coming up, you got the best Excel content on TH-cam man keep it up! Videos are straight to the point and useful.
Appreciate it man! Best of luck 👊
I have watched different videos to learn and understand index match function. Finally understand. Thanks a lot for teaching in easy way. Keep up the good work.
The fact that your examples are clear and concise makes it the best tutorial yet.
Thanks for the simplified and concise explanation, Kenji!
bro, you explain the best so far! It really helped with the examples of countries, months, and salaries!
Thanks so much Kenji! I watched few other people's videos but still didn't get. You made it easy to understand !
Just got myself into more advanced Excel and this is amazing, easy to understand and full of tips that are usefull, perfect....
Good morning, Sir. Thank you for your hard work. I personally have learned a lot from your videos on TH-cam here. It's 2:14 AM here in Sierra Leone local time. I Strongly want to become a Business data Analyst.
Once again thank you very much and God Bless you
I watched multiple videos on TH-cam for however, your video was super simple and well-explained. Thank you so much for creating such videos and educating us.
In the Bonus, can we use another formula such as Sumifs instead of using filter??
How would y do it?
Great tutorial, very simple and easy to follow.
One small comment for the bonus example, I believe a “sumproduct” function with 3 conditions will be a better option to use
For the last 5. Bonus spreadsheet, a simple SUMIF also does the trick!
=SUMIF(B10:B28,C3,C10:C28)
but it wouldn't work (change dynamically) if you change C4 to another month than January.
For the last function instead of using filter function twice we could also use * as AND operator
That formula of yours gives a #value error!
its giving #value error when using * or and operator dont know why
thank you so much kenji. I was able to easily understand the index function. you're very good at teaching.
You are a life saver I have spent hours on my homework thank you so much!
I was mind blown by the bonus tip at the end. Great explanation of excel functions
Amazing video, I observe one thing that is at the Advanced IndexMatch, after typing the function we need to pressing Ctrl + Shift + Enter instead of just enter🙂
Thank you, this was very helpful. I am still a little confused about the $ to fix columns and rows, but other than that I was able to create my little project :)
Interesting this video was posted on my birthday. This is really good info. Thank you. 😊
Appreciate the clear explanation from you, Kenji
I know people , you are a bit different. I don't know exactly how but It appears everything is clear for me. thank you so much,
As always, you have continually help improve my knowledge in excel. 👍 Job
Hi Kenji, what a great video to upgrade my excel skill.
Could you please explain what is the pros & cons using function of vlookup, offset and index match in linking a huge database? Thanks
Nicely done! One question: why are you not using XMATCH?
One of the most important useful videos which explained Index+match functions
Glad it was helpful!
Thanks for the tutorial. Regarding an advanced example, I did the exact as you did but I got an error. Any tips, please? Thanks again
You've made this video at the perfect time! Subscribed
Awesome! Thank you!
What's the difference between xlookup and index functions? What advantages does one has over the other?
I just saw your msg right after I posted the same questions. Great minds think alike 😉
Amazing. Thank you Kenji.
GREAT VIDEO!!! For #4 Advanced, I just put in a third "MATCH" function and it seems to be working. Is there a reason you don't prefer this? =INDEX(C10:K29,MATCH(C3,B10:B29,0),MATCH(C4,C8:K8,0),MATCH(C5,C9:K9,0))
thanks. learned new things
u can use it also like that , but to make it short he used & ,,,,,
This is a well explained index match tutorial.
Wonderful explanation! For the bonus part, I was thinking of using a pivot table instead. 🤔
HI , I am preeti sharmafrom indai and i recently joined your page and so happy to learn new things from your page.
The xlookup function is very powerful which addresses most of the limitations the vlookup/hlookup had. Can u pls tell me how Index Match is still better, I had the impression xlookup also addresses Index Match
Greetings, That was an interesting question. I've reviewed your question and here is my thought. The Limitation of XLOOKUP is, it cannot handle two dimensional data while searching for the values unlike the index and match does. Meaning in XlOOKUP only one specific column can be chosen where our search search criteria exists. Hope that answers the question.
@@venkataramanasimham5514 many thanks 🙏
Hi Kenji, do you know how to use the index match formula with 2 arguments but when 1 argument is that the reference cell needs to contain words from the array, but the reference cell can contain those words and other words as well?
Love your tutorials!😊
The bonus model is great! Thanks for the tutorial! Can you make a video showing how to link any 1 of the models to a power point deck, dynamically?
Question, when you do index match's does it matter which critera you match first i.e. for the dynamic index match example where your first match is based on revenue,GP and Net income and the 2nd match is based on the year (2020 and 2022), could I switch the matches around i.e. the first match being based on the years (2020 & 2022) and the second being on revenue, GP and Net Income?
Is it possible to do a sumifs formula within the the entire index? For example, say there were two "Spain" in the left column with the value of 7,000 and 4,000 for March 2021. Is there a way to build in the sumifs into the entire index so that 11,000 is the answer?
Just to say thank you , you have beeing of great help to me
Awesome, especially the 5th one
Nicely explained. Thanks Kenji!
Love the bonus scenario!
Great explanation. Thank you. You make it easier❤
Thank you for this. The file also helped practice and polish my formula so much. ❤❤
Thanks a lot for this video, it was extremely helpful.
Hey Kenji, thanks for this great video! I was wondering if you could tell us how to lookup a number if we have a minimum and maximum range given. For example, if any zip code's between 99501 & 99950 it'd be under the state of Alaska! How can I look up the state if I were given a bunch of postal codes.
Very nicely explained, thank you.
hi kenji..your from where? thanks for this index match tutorials.
after multiple attemts i came to realse that the formual for the forth exercise is not working in 2019 version of excel.
same here 🥲
Use index fn array in match fn lookup array
It only works in 2021 and 365
Same here. I wasted so much time. Then what's the alternative to this formula
Lol I have MS Excel 2013 😭🤣
Great one, Kenji! index/match competes with xlookup. is level 4 possible for xlookup?
Supernice Tipp about the double criteria!
what are differences between xlookup and index/match approach?
is there a way to solve bonus part by using sumifs?
Hi Bro! Is it possible to add 3 in the bonues Scenario ? =SUM(FILTER(FILTER(B20:M22,A20:A22=A15),B19:M19=C15,)) please let me know if is possible thank you
Thank you for this impressive instruction.
Hi, Is it possible to ignore Steven in your last example when looking up the data and returning the minimum sales value for the remaining Salesperson?
Hi Kenji, awesome tutorial!!!! I need some help if I may ask you with extracting the data from two tables which contains date, client, amount. The data set is not consistent because the client invoice amount is different to the billable amount. Which formula will I use to get the invoice amount specific to a client against the billable amount?
i would use pivot table
Hi Index match or Xlookup? whats the best
for the first example, xlookup can also be used and easier?
some of us dont use the newer versions of excel and hence don't have access to functions like filter. So how do we go about the bonus part? I think SUMIF should work but if not then how do we do it?
sumif and sumifs didn't work with horizontal arrays.
Hey Kenji, any chance you can create an SQL Course? So many finance/accounting analyst roles are now asking candidates to know SQL. I would totally buy an SQL course if you made one. If not, i would love to hear from you if there is an SQL course you recommend
Thanks for your wonderful explanation..just a suggestion...MATCH is not pronounced as MUCH😊🎉🎉🎉❤❤❤
Hi Kenji, great video as always but using sumifs for your last example is simplier than filter.
Thank you! The SUMIFS unfortunately doesn't work. That's because the criteria ranges and the sum range don't have the same number of rows and columns (feel free to try it and let me know if otherwise though)
@@KenjiExplains Indeed ! you are the best !
Wow, perfect tutorial! How do you find these things? :D
I am interested in the DYNAMIC DATASET MATCH problem. I have a similar dataset which requires the same formula construction. The key difference is that I am working with TABLES. Therefore, the issue I face is that the first LOOKUP_ARRAY keeps moving as i drag my formula horizontally. There does not seem to be anyway to 'lock' this array as in the case with $. Any tips/ solutions?
Great video. i didnt know that you can add & to combine the lookup values in indexmatch
Thank you, quite a cool feature!
Can I use a filter with an index?
Thanks 👍 you help me with this formula 👍
crucial example thank you
Hi Kenji. I would like to ask a question in excel which i was not able to solve in an interview where i had to pull sales value of a country in a category and another category for different years. example: sales value of coffee in US when sales value and volume are given. so like we had 3 columns to look from and one row of year. how do we solve this. could you please help
What if there’s multiple results? For example clients and what company they work for. Our Index I think would be the clients names, if we’re sorting by company.. match would be companies name.. but only one clients name comes up even if I drag out the formula. How can I get all the clients names that work at that company?
When I'm trying to use Index and Match, while referencing an array table on another worksheet, I'm getting problems. Any tips?
Thanks Kenji, very useful. Although Xlookup would be my go to function now!
Thank you and great choice! (unfortunately some people don't have the XLOOKUP due to their older excel versions, so hopefully this helps them)
How do you use Xlookup if you have multiple criteria and ranges?
@@AsfandyarAhmed-l3h use the &
thank you for sharing
Thanks for the video. Very helpful
Life Saver!
Great job
is it possible to add another filter in this =SUM(FILTER(FILTER(B20:M22,A20:A22=A15),B19:M19=C15,))
Thank you!
I can't seem to get the & to work on my formula... Any suggestions?
For the advanced "Index Match", I do get an error which says"#Value!" Is there any error that I do? the formula that I enter in the same sheet of yours.
=INDEX(C10:K29,MATCH(C3,B10:B29,0),MATCH(C4&C5,C8:K8&C9:K9,0))
It only work in 2021 or 365 office version. mine is 2019 and it is not working.
Brilliantly explained!!!
Thank you! Glad you think so!
you could've used SUMIFS on the last formula right?
Instead of Filter function (avaialble only in 365 and later), we can use sumproduct, which is useful in other versions also.
Thanks Kenji
Awesome bro , Brilliant Video !!
Thank you!
Hi, I have got a small doubt. In the last section, you've given a filtered array in the FILTER() to get for January month. However, the filtered array doesn't have any header for the months. How's it giving results then?
If you look at the 2nd argument for the outer filter function it's referencing C9:J9 = C4, that's where it is getting it from, so it's not the filtered array but rather the original one it is looking up for the month.... at least I think so.. also when you filter an array normally it doesn't get rid of the headers
Useful .Thanks Man ...
filter option is not available for me. what to do?
Loved the explanation Kenji! You've got a subscriber! Question - I use google sheets and not excel, do you have courses on Google sheets only?
Thank you! Unfortunately, I don't have a google sheets course but it's something to consider. Can I ask why you use google sheets? is it because your company uses it, because it's free, or something else?
@@KenjiExplains My company uses Google sheets
3 criteria formula is not working in excel 2007 ( 3 criteria) where you use &
Will this not work or i am doing something wrong because i match my formula was exactly same with you...
same :(
Brilliant!
Super helpful !!
Awesome video
can you do xlookup tutorial as well?
I already have one here: th-cam.com/video/3MdPSHkyfdI/w-d-xo.html
could you just use the ampersand for a 2 condition index match so you only have 1 match function?
great question! Unfortunately, you can't because the index function has 2 key arguments which are the row and column. So if you try to feed the row argument both a row and a column using the ampersand it doesn't work.
@@KenjiExplains thanks for the answer
For 1st criteria, we can also use only the index function to get an answer then why did you choose index match
Great Video! Awesome!