The ULTIMATE Index Match Tutorial (5 Real-World Examples)
ฝัง
- เผยแพร่เมื่อ 4 มิ.ย. 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 TH-cam 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.
The most understandable explanations of EXCEL formula use on TH-cam. Thanks Kenji!
Glad it was helpful!
Thanks so much Kenji! I watched few other people's videos but still didn't get. You made it easy to understand !
bro, you explain the best so far! It really helped with the examples of countries, months, and salaries!
Thank you for this impressive instruction.
Just got myself into more advanced Excel and this is amazing, easy to understand and full of tips that are usefull, perfect....
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 👊
As always, you have continually help improve my knowledge in excel. 👍 Job
Interesting this video was posted on my birthday. This is really good info. Thank you. 😊
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
For the last function instead of using filter function twice we could also use * as AND operator
Nicely explained. Thanks Kenji!
You are a life saver I have spent hours on my homework thank you so much!
Great explanation. Thank you. You make it easier❤
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!
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?
Just to say thank you , you have beeing of great help to me
This is a well explained index match tutorial.
Thanks 👍 you help me with this formula 👍
Great video. i didnt know that you can add & to combine the lookup values in indexmatch
Thank you, quite a cool feature!
crucial example thank you
Super helpful !!
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.
Great video!! Super helpful as always
Useful .Thanks Man ...
One of the most important useful videos which explained Index+match functions
Glad it was helpful!
You've made this video at the perfect time! Subscribed
Awesome! Thank you!
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 for the video. Very helpful
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 &
Thanks Kenji
amazing technics
Brilliantly explained!!!
Thank you! Glad you think so!
In the Bonus, can we use another formula such as Sumifs instead of using filter??
How would y do it?
Awesome bro , Brilliant Video !!
Thank you!
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?
Life Saver!
thanks you are the best
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.
Wow, perfect tutorial! How do you find these things? :D
Great Video! Awesome!
HI , I am preeti sharmafrom indai and i recently joined your page and so happy to learn new things from your page.
for the first example, xlookup can also be used and easier?
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
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?
very good for me
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 kenji..your from where? thanks for this index match tutorials.
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?
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...
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. 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
Bro please make video on slicers custom color setting.
It's amaising!
Instead of Filter function (avaialble only in 365 and later), we can use sumproduct, which is useful in other versions also.
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 🙏
Hey another awesome video 👌
Thank you for the nice words!
@@KenjiExplains 🙏
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
HELLO THANKS 🥰😍😘
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
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 😉
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.
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 & ,,,,,
Thank You Bro,Can I know you are a trainer?
I can't seem to get the & to work on my formula... Any suggestions?
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 😭🤣
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?
is it possible to add another filter in this =SUM(FILTER(FILTER(B20:M22,A20:A22=A15),B19:M19=C15,))
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
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
filter option is not available for me. what to do?
you could've used SUMIFS on the last formula right?
can you do xlookup tutorial as well?
I already have one here: th-cam.com/video/3MdPSHkyfdI/w-d-xo.html
Why use this over xlookup?
I think text-join to join the dates is a bit easier
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 !
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.
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 😊 ..
I am getting an error in the Advanced Index Match column.
Might be wrong but I get the feeling that you can solve all of these way easier with a QUERY function.
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 :(
Why can't we use sumifs?
Pivot is a better option to this i think... Correct me if i am wrong
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
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.
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)
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
I guess the last one is more easy with sumproduct function
Great suggestion!
Mind if I ask how you would formulate that? I'm just not seeing it...
Lol))) Haven't you heard about the SUMIF function?))
Hey there, 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...)
The bonus function could also be a sumif index match