The ULTIMATE Index Match Tutorial (5 Real-World Examples)
Vložit
- čas přidán 2. 08. 2024
- Learn the Index Match function in Excel to look for any value in a dataset
👉 Take our Excel Course! www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video: view.flodesk.com/pages/64ac10...
In this video you'll learn all about the index match function using five real-world scenarios you might encounter at work. First, we'll go over a simple index match with one criteria. Then, we'll go over an index match with two criteria, meaning we'll need two match functions (one for the row and one for the column). Third, we'll go over a dynamic index match where we'll use relative referencing to apply the same function across multiple ranges. Fourth, we'll go through an advanced index match example with three criteria by combining the index match with the ampersand. Finally, we'll go over the main limitation of the index match with an alternate solution using the SUM and FILTER combinations.
LEARN:
🔥Power BI for Business Analytics: www.careerprinciples.com/cour...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
🚀 All our courses: www.careerprinciples.com/courses
SOCIALS:
🎬 My Company CZcams Channel: / @careerprinciples
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑💻 LinkedIn - / careerprinciples
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Simple Index Match
1:44 - Index Match with Two Criteria
3:31 - Dynamic Index Match
6:57 - Advanced Index Match
9:05 - Bonus Function
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
👉 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
The most understandable explanations of EXCEL formula use on CZcams. Thanks Kenji!
Glad it was helpful!
bro, you explain the best so far! It really helped with the examples of countries, months, and salaries!
Just got myself into more advanced Excel and this is amazing, easy to understand and full of tips that are usefull, perfect....
Thanks so much Kenji! I watched few other people's videos but still didn't get. You made it easy to understand !
I have a job interview coming up, you got the best Excel content on CZcams man keep it up! Videos are straight to the point and useful.
Appreciate it man! Best of luck 👊
As always, you have continually help improve my knowledge in excel. 👍 Job
I watched multiple videos on CZcams for however, your video was super simple and well-explained. Thank you so much for creating such videos and educating us.
You are a life saver I have spent hours on my homework thank you so much!
Thank you for this. The file also helped practice and polish my formula so much. ❤❤
Great explanation. Thank you. You make it easier❤
For the last function instead of using filter function twice we could also use * as AND operator
Interesting this video was posted on my birthday. This is really good info. Thank you. 😊
Nicely explained. Thanks Kenji!
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 was mind blown by the bonus tip at the end. Great explanation of excel functions
Love your tutorials!😊
Just to say thank you , you have beeing of great help to me
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
This is a well explained index match tutorial.
Great video!! Super helpful as always
Thank you for this impressive instruction.
Super helpful !!
Thanks a lot for this video, it was extremely helpful.
Very nicely explained, thank you.
Useful .Thanks Man ...
You've made this video at the perfect time! Subscribed
Awesome! Thank you!
crucial example thank you
One of the most important useful videos which explained Index+match functions
Glad it was helpful!
Brilliant!
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?
Thanks 👍 you help me with this formula 👍
Thanks for the video. Very helpful
Brilliantly explained!!!
Thank you! Glad you think so!
Life Saver!
Great video. i didnt know that you can add & to combine the lookup values in indexmatch
Thank you, quite a cool feature!
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
Thanks Kenji
Awesome video
HI , I am preeti sharmafrom indai and i recently joined your page and so happy to learn new things from your page.
amazing technics
Great Video! Awesome!
thanks you are the best
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?
It's amaising!
Awesome bro , Brilliant Video !!
Thank you!
very good for me
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.
In the Bonus, can we use another formula such as Sumifs instead of using filter??
How would y do it?
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?
@@user-ey6wm7lk8m use the &
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.
for the first example, xlookup can also be used and easier?
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
Wow, perfect tutorial! How do you find these things? :D
Hey another awesome video 👌
Thank you for the nice words!
@@KenjiExplains 🙏
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
Nicely done! One question: why are you not using XMATCH?
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?
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
Instead of Filter function (avaialble only in 365 and later), we can use sumproduct, which is useful in other versions also.
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?
HELLO THANKS 🥰😍😘
Bro please make video on slicers custom color setting.
hi kenji..your from where? thanks for this index match tutorials.
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
is there a way to solve bonus part by using sumifs?
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
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.
can we use the SUMIFS function for the bonus question? really been struggling with this and would appreciate your help.
And what would you recommend if we have multiple January in the months row just like Steven? I have been trying to solve this since 2 weeks...
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 😉
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 Index match or Xlookup? whats the best
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 & ,,,,,
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 😭🤣
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
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
filter option is not available for me. what to do?
I can't seem to get the & to work on my formula... Any suggestions?
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?
Thank You Bro,Can I know you are a trainer?
can you do xlookup tutorial as well?
I already have one here: czcams.com/video/3MdPSHkyfdI/video.html
I think text-join to join the dates is a bit easier
Why use this over xlookup?
you could've used SUMIFS on the last formula right?
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
I am getting an error in the Advanced Index Match column.
In dynamic index match when dragging I won’t getting answer in all columns why?
After again I changed formula on 2022 revenue coloumn then I dragged after that I got answer 😊 ..
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 !
Pivot is a better option to this i think... Correct me if i am wrong
is it possible to add another filter in this =SUM(FILTER(FILTER(B20:M22,A20:A22=A15),B19:M19=C15,))
Why can't we use sumifs?
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.
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 :(
For 1st criteria, we can also use only the index function to get an answer then why did you choose index match
the download is not working. can you please fix that? thank you!
Hey just checked seems to work fine. Please check your downloads folder for the excel file. You'll also receive it via email.
Might be wrong but I get the feeling that you can solve all of these way easier with a QUERY function.
advanced index match is not getting as you said its coming #value!
& filter formula don't exist in my excel why?
that is bcoz we need to convert the formula into an array formula, otherwise you will see a #VALUE! error.
Once you've entered the formula, press Ctrl+Shift+Enter to resolve the issue
don't you need to include the table headings? I am look at a table with different headings and data and need to use index match for that
Why not just use SUMIFS fuction in the bonus example?
Hey! 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)
wouldn't it be easier to do these calculations with a pivot table?
Yes, but pivot table also has its own limitations. When you change the data in the source table, you need to refresh the pivot table. But you don't need to refresh anything with these formulas, as the formulas will update automatically. This could be helpful if you are doing a lot of change in your source data.
Me also has the same doubt... But clear now