The ULTIMATE Index Match Tutorial (5 Real-World Examples)

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 KZbin 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.

[@KenjiExplains]

👉 See all our courses here! www.careerprinciples.com/courses

[@huzaimamughal6603]

as a professional what would you comment on CFA as a course?

[@obraata567]

Hello can you make a progress report or class record of students in different levels using this index matching

[@altair769]

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.

[@ccenews8980]

more explanation on how many times f4 is pressed and when

[@stevenchappuis4103]

The most understandable explanations of EXCEL formula use on KZbin. Thanks Kenji!

[@KenjiExplains]

Glad it was helpful!

[@Moiez101]

bro, you explain the best so far! It really helped with the examples of countries, months, and salaries!

[@chatureshpatel1159]

Just got myself into more advanced Excel and this is amazing, easy to understand and full of tips that are usefull, perfect....

[@wendycanfield9266]

Thanks so much Kenji! I watched few other people's videos but still didn't get. You made it easy to understand !

[@yohannaagbanaji8012]

As always, you have continually help improve my knowledge in excel. 👍 Job

[@tommyluu5677]

I have a job interview coming up, you got the best Excel content on KZbin man keep it up! Videos are straight to the point and useful.

[@KenjiExplains]

Appreciate it man! Best of luck 👊

[@hasanahbeauty3096]

Great explanation. Thank you. You make it easier❤

[@preetiwakchoure]

Nicely explained. Thanks Kenji!

[@gurpreetkaur4782]

I watched multiple videos on KZbin for however, your video was super simple and well-explained. Thank you so much for creating such videos and educating us.

[@sabpebhari]

For the last function instead of using filter function twice we could also use * as AND operator

[@deepjyotistories]

Thank you for this. The file also helped practice and polish my formula so much. ❤❤

[@gos_ka]

Love your tutorials!😊

[@ArianaHernandez-bl4pj]

You are a life saver I have spent hours on my homework thank you so much!

[@NihilPraveen]

I was mind blown by the bonus tip at the end. Great explanation of excel functions

[@questionresearch8721]

Interesting this video was posted on my birthday. This is really good info. Thank you. 😊

[@roberth.9558]

Thank you for this impressive instruction.

[@nazuada]

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 :)

[@yuvarajtalwade]

Very nicely explained, thank you.

[@levyfreitas3882]

I'm brazilian and this video is AMAZING, the "Advanced Index" help me a lot!! Bro you explain the best so far! Thanks Kenji.

[@KenjiExplains]

Happy to help!

[@iwanrudyanto4415]

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

[@inioluwaomilaju1382]

Thanks a lot for this video, it was extremely helpful.

[@abiel237]

Just to say thank you , you have beeing of great help to me

[@SajalManandhar]

Super helpful !!

[@jmcfit9830]

This is a well explained index match tutorial.

[@camlex6310]

Great video!! Super helpful as always

[@yakobdereje4640]

crucial example thank you

[@rcasturi55]

Useful .Thanks Man ...

[@mghector6430]

Thanks 👍 you help me with this formula 👍

[@LukeMunkombwe]

Brilliant!

[@i.5mviews923]

amazing technics

[@melissad3390]

Awesome video

[@manuelvega-arango599]

Thanks for the video. Very helpful

[@rexbk]

Thanks Kenji

[@Hezzlington]

You've made this video at the perfect time! Subscribed

[@KenjiExplains]

Awesome! Thank you!

[@mohammadalbizri2013]

One of the most important useful videos which explained Index+match functions

[@KenjiExplains]

Glad it was helpful!

[@llaw785]

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?

[@kemalserkanozturk9060]

thanks you are the best

[@drewsoffice]

Great Video! Awesome!

[@thediscrete]

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

[@b.r.srihari4099]

Brilliantly explained!!!

[@KenjiExplains]

Thank you! Glad you think so!

[@dennisjohncastillo6081]

Life Saver!

[@vincerau]

Great video. i didnt know that you can add & to combine the lookup values in indexmatch

[@KenjiExplains]

Thank you, quite a cool feature!

[@milanpatel4035]

For the last 5. Bonus spreadsheet, a simple SUMIF also does the trick! =SUMIF(B10:B28,C3,C10:C28)

[@nhano8042]

but it wouldn't work (change dynamically) if you change C4 to another month than January.

[@KoLin-ut8xq]

very good for me

[@bubblebath2892]

Awesome bro , Brilliant Video !!

[@KenjiExplains]

Thank you!

[@preetisharma484]

HI , I am preeti sharmafrom indai and i recently joined your page and so happy to learn new things from your page.

[@allsaka]

It's amaising!

[@MuhammadNurbasit]

Wow, perfect tutorial! How do you find these things? :D

[@randomperson2160]

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?

[@gerbherb8215]

Nicely done! One question: why are you not using XMATCH?

[@EvelynSann]

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

[@santiagozapata6898]

In the Bonus, can we use another formula such as Sumifs instead of using filter??

[@vin7281]

How would y do it?

[@joblesstess]

for the first example, xlookup can also be used and easier?

[@PerkinsGreg]

Thanks Kenji, very useful. Although Xlookup would be my go to function now!

[@KenjiExplains]

Thank you and great choice! (unfortunately some people don't have the XLOOKUP due to their older excel versions, so hopefully this helps them)

[@user-ey6wm7lk8m]

How do you use Xlookup if you have multiple criteria and ranges?

[@mrdimosssssss]

@@user-ey6wm7lk8m use the &

[@rachitbharti6254]

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.

[@flightforge30]

hi kenji..your from where? thanks for this index match tutorials.

[@dennisogsomer7426]

HELLO THANKS 🥰😍😘

[@Ruphan92]

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?

[@gauravbharti9676]

is there a way to solve bonus part by using sumifs?

[@user-wo3mu9bc4j]

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?

[@raghavwasan7562]

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?

[@ragnargaming3292]

Bro please make video on slicers custom color setting.

[@josephthmas2070]

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?

[@Atos.1]

i would use pivot table

[@santroramki]

Instead of Filter function (avaialble only in 365 and later), we can use sumproduct, which is useful in other versions also.

[@Ari-lu5ve]

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

[@nishanth4323]

Hey another awesome video 👌

[@KenjiExplains]

Thank you for the nice words!

[@nishanth4323]

@@KenjiExplains 🙏

[@Danny-Do-It]

Hi Index match or Xlookup? whats the best

[@ankitamurarka3467]

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

[@cshahed]

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

[@venkataramanasimham5514]

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.

[@cshahed]

@@venkataramanasimham5514 many thanks 🙏

[@Raelynn322]

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?

[@user-ur6ot2xl8y]

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?

[@r.bharatmurthy8512]

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

[@KoLin-ut8xq]

Thank You Bro,Can I know you are a trainer?

[@MaxNeil-qj8mp]

can we use the SUMIFS function for the bonus question? really been struggling with this and would appreciate your help.

[@MaxNeil-qj8mp]

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...

[@adnciclismo]

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

[@faithfulinspiration593]

What's the difference between xlookup and index functions? What advantages does one has over the other?

[@cshahed]

I just saw your msg right after I posted the same questions. Great minds think alike 😉

[@Thomazbr]

you could've used SUMIFS on the last formula right?

[@VamsiKusa]

after multiple attemts i came to realse that the formual for the forth exercise is not working in 2019 version of excel.

[@talibbb13]

same here 🥲

[@NoMrcyGOD179]

Use index fn array in match fn lookup array

[@rizkhanrikaz6610]

It only works in 2021 and 365

[@saiteka]

Same here. I wasted so much time. Then what's the alternative to this formula

[@shweta-bisht]

Lol I have MS Excel 2013 😭🤣

[@davidtanner4783]

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))

[@Soya_Azashiro]

thanks. learned new things

[@RohitSingh-sd8fx]

u can use it also like that , but to make it short he used & ,,,,,

[@katefenters2605]

I can't seem to get the & to work on my formula... Any suggestions?

[@krishnasadasivan4011]

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?

[@KenjiExplains]

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?

[@krishnasadasivan4011]

@@KenjiExplains My company uses Google sheets

[@arunmm4777]

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?

[@santroramki]

sumif and sumifs didn't work with horizontal arrays.

[@adnciclismo]

is it possible to add another filter in this =SUM(FILTER(FILTER(B20:M22,A20:A22=A15),B19:M19=C15,))

[@yashasgowda6585]

filter option is not available for me. what to do?

[@asvpxjase]

Why use this over xlookup?

[@lucabenattar7365]

Hi Kenji, great video as always but using sumifs for your last example is simplier than filter.

[@KenjiExplains]

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)

[@lucabenattar7365]

@@KenjiExplains Indeed ! you are the best !

[@willzinner8813]

can you do xlookup tutorial as well?

[@KenjiExplains]

I already have one here: kzbin.info/www/bejne/aX7HgYZ-oN6Zmqs

[@aarongalef4195]

I think text-join to join the dates is a bit easier

[@pradyumnakulkarni2928]

For 1st criteria, we can also use only the index function to get an answer then why did you choose index match

[@vijeshkvijayan5320]

Pivot is a better option to this i think... Correct me if i am wrong

[@JafacaksWasTaken]

could you just use the ampersand for a 2 condition index match so you only have 1 match function?

[@KenjiExplains]

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.

[@JafacaksWasTaken]

@@KenjiExplains thanks for the answer

[@roysimon982]

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))

[@Wanaw-i7y]

It only work in 2021 or 365 office version. mine is 2019 and it is not working.

[@ansariozair8459]

Why can't we use sumifs?

[@thehillcaliboy7855]

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...

[@talibbb13]

same :(

[@happykidsains4168]

In dynamic index match when dragging I won’t getting answer in all columns why?

[@happykidsains4168]

After again I changed formula on 2022 revenue coloumn then I dragged after that I got answer 😊 ..

[@mamunahmed892]

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

[@user-tt5ch5ei1t]

I am getting an error in the Advanced Index Match column.

[@privatedaNiel]

wouldn't it be easier to do these calculations with a pivot table?

[@thesenate8268]

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.

[@vijeshkvijayan5320]

Me also has the same doubt... But clear now

[@suvroj645]

the download is not working. can you please fix that? thank you!

[@KenjiExplains]

Hey just checked seems to work fine. Please check your downloads folder for the excel file. You'll also receive it via email.

[@alekseigerasimov9073]

Why not just use SUMIFS fuction in the bonus example?

[@KenjiExplains]

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)