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 RUclips 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 RUclips 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
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?
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?
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.
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?
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 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))
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
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
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
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 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?
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)
👉 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 RUclips. 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 RUclips man keep it up! Videos are straight to the point and useful.
Appreciate it man! Best of luck 👊
bro, you explain the best so far! It really helped with the examples of countries, months, and salaries!
The fact that your examples are clear and concise makes it the best tutorial yet.
Just got myself into more advanced Excel and this is amazing, easy to understand and full of tips that are usefull, perfect....
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.
Thanks so much Kenji! I watched few other people's videos but still didn't get. You made it easy to understand !
Thanks for the simplified and concise explanation, Kenji!
Good morning, Sir. Thank you for your hard work. I personally have learned a lot from your videos on RUclips 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
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
I watched multiple videos on RUclips for however, your video was super simple and well-explained. Thank you so much for creating such videos and educating us.
thank you so much kenji. I was able to easily understand the index function. you're very good at teaching.
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 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
In the Bonus, can we use another formula such as Sumifs instead of using filter??
How would y do it?
Amazing. Thank you Kenji.
Appreciate the clear explanation from you, Kenji
As always, you have continually help improve my knowledge in excel. 👍 Job
You've made this video at the perfect time! Subscribed
Awesome! Thank you!
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,
Interesting this video was posted on my birthday. This is really good info. Thank you. 😊
HI , I am preeti sharmafrom indai and i recently joined your page and so happy to learn new things from your page.
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.
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 :)
Wonderful explanation! For the bonus part, I was thinking of using a pivot table instead. 🤔
This is a well explained index match tutorial.
Awesome, especially the 5th one
Love the bonus scenario!
Love your tutorials!😊
Great explanation. Thank you. You make it easier❤
Nicely explained. Thanks Kenji!
One of the most important useful videos which explained Index+match functions
Glad it was helpful!
Just to say thank you , you have beeing of great help to me
Great job
Thank you for this impressive instruction.
Very nicely explained, thank you.
Thanks a lot for this video, it was extremely helpful.
thank you for sharing
Thank you for this. The file also helped practice and polish my formula so much. ❤❤
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. i didnt know that you can add & to combine the lookup values in indexmatch
Thank you, quite a cool feature!
Thank you!
crucial example thank you
Brilliantly explained!!!
Thank you! Glad you think so!
Thanks 👍 you help me with this formula 👍
Nicely done! One question: why are you not using XMATCH?
Thanks for the video. Very helpful
Awesome bro , Brilliant Video !!
Thank you!
Awesome video
Thanks Kenji
Useful .Thanks Man ...
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?
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?
Brilliant!
Thanks for your wonderful explanation..just a suggestion...MATCH is not pronounced as MUCH😊🎉🎉🎉❤❤❤
Super helpful !!
hi kenji..your from where? thanks for this index match tutorials.
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
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 &
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?
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 😉
Can I use a filter with an index?
Wow, perfect tutorial! How do you find these things? :D
amazing technics
Life Saver!
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 🙏
is there a way to solve bonus part by using sumifs?
Hi Index match or Xlookup? whats the best
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?
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 😭🤣
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 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 & ,,,,,
Great video!! Super helpful as always
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
When I'm trying to use Index and Match, while referencing an array table on another worksheet, I'm getting problems. Any tips?
Great Video! Awesome!
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
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
Pivot is a better option to this i think... Correct me if i am wrong
for the first example, xlookup can also be used and easier?
very good for me
Bro please make video on slicers custom color setting.
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?
I can't seem to get the & to work on my formula... Any suggestions?
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
thanks you are the best
filter option is not available for me. what to do?
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?
Instead of Filter function (avaialble only in 365 and later), we can use sumproduct, which is useful in other versions also.
Thank You Bro,Can I know you are a trainer?
can you do xlookup tutorial as well?
I already have one here: ruclips.net/video/3MdPSHkyfdI/видео.html
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 !
Hey another awesome video 👌
Thank you for the nice words!
@@KenjiExplains 🙏
HELLO THANKS 🥰😍😘
you could've used SUMIFS on the last formula right?