How To Use Index Match As An Alternative To Vlookup

Рет қаралды 1,589,162

Күн бұрын

Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video I explain how to use the INDEX MATCH formula as an alternative to VLOOKUP in Excel. Download the Excel file: www.excelcampus.com/functions...
You can follow along with the video and practice writing the formulas. The Excel file also includes my cheat sheet for writing the formulas.
I explain the two main benefits of INDEX MATCH over VLOOKUP. This includes:
The ability to return a value from a column to the left/above.
Separate columns for the lookup and return ranges.
We also look at the most common error that the formula returns and how to prevent it.
The new XLOOKUP function is another alternative to VLOOKUP and INDEX MATCH. Check out my other videos for more details on that.
VLOOKUP Explained: • Excel Vlookup Tutorial...
XLOOKUP Explained: • Xlookup In Excel: Expl...
XLOOKUP Explained in 3 Minutes: • Xlookup In Excel: Expl...
Excel Tables for Beginners: • Excel Tables Tutorial:...
#MsExcel #ExcelCampus
00:00 Introduction
00:39 Benefits of Index Match
02:23 Match Function
06:02 Index Function
09:09 Index Match
12:20 Index Match to the Left
15:13 Most Common Error

Пікірлер: 669

@LearningPoint360 4 жыл бұрын

That's the best one I have came through... and you exactly know what we need. A 'Thanks' would just be a very little expression of my gratefulness.

@ExcelCampus 4 жыл бұрын

You're very welcome! 😀

@szt24 4 жыл бұрын

i have to say this is by far THE BEST tutorial on index match i have seen. So clear, so simple to understand. Your teaching style and accompanying graphics and cheat sheets make it easy to follow. Thank you!!!

@ManojPathakCMA 2 жыл бұрын

]

@eluxury2 Жыл бұрын

I've seen other Excel teaching videos. This guy is the best in every way, especially on complex functions. The materials he provided were excellent!!

@garynair5143 3 жыл бұрын

As an instructor,showing things that go wrong first gives students a better sense of WHY they need to learn an alternative or learn it the way its supposed to work. Some bad habits get so ingrained that unless theres a reason, they keep doing things the old way. So kudos to you.

@eleenaswift8412 5 ай бұрын

True

@elisabethwestner3953 2 жыл бұрын

OMG, thank you SO much for this video! Back story: I used INDEX MATCH several years ago at a previous job, many times for a few weeks and then not at all since then. Now at my new job I absolutely _KNEW_ that this was the function I needed and I read several blogs and watched some videos about how to do it, but nothing was working right. This video explained it all so well and 10 minutes after watching it I solved the issue I was having for weeks and you've saved me!!!!! *THANK YOU SO MUCH!!!!!!*

@cherposton8242 3 жыл бұрын

I am a very competent Excel user when people give good wxplanaon how to do something. I've found that most tutorials go to fast or don't explain every step. I felt you did a great job of explaining and I appreciate what you were able to teach. Thank you.

@ddigwell 2 жыл бұрын

I just reviewed the accompanying workbook you provided. I believe your generosity with your knowledge to be one of the finer examples of what the internet was intended to be and I loathe the times I have fallen short of that expectation. I truly appreciate you, your work, and for reminding me to be human on the internet.

@priscillapedraza3341 5 ай бұрын

This has to be the best channel for excel tutorials. Very clear and easy steps to follow. You don't over complicate it. Thank you

@prakashviswanathan5936 4 жыл бұрын

I watched several videos before stumbling upon this one. This is the best and easy tutorial on index matching. Thank you Jon

@Felix-ou3xe 4 жыл бұрын

This is such a great tutorial on INDEX MATCH. I love that this broke down each function's definition and even put the requirements in layman's term for anyone without coding experience. The snippet about using data validation for pulling up values was brilliant. Definitely is another great idea for my toolbox. Thank you for the awesome, well-articulated video.

@glennlehman3527 3 жыл бұрын

As someone named Lehman I really appreciated the video.

@publicguy1664 2 жыл бұрын

This isn't really coding.

@GarconeRJ 4 жыл бұрын

This is BY FAR, the best explanation I ever found on this topic. Thanks for sharing.

@robertjones5669 Жыл бұрын

This is the best explanation ever. Thank you - I'm using it to compare inventory audit sheets to a list of 26,000 items unaccounted for. Joy. This makes that task digestible! Thank you again.

@ExcelCampus Жыл бұрын

Glad it was helpful! 😀

@user-kh5xo1ne2u Ай бұрын

LOVED THE CONTENT AND YOU EXPLAINATION ALOT, ONE MORE THING I HAVE NOTICED IS THE WAY YOU ARRANGE THE THOUGHTS TO INSTALL THE FORMULATS CORRECTLY IN OUR MINDS THANKS ALOT

@ExcelCampus Ай бұрын

Thanks for the feedback! 😀

@infoTECH.Services 2 жыл бұрын

It was my first time to be given those functions of index and match exercise without teaching but later I thought all class after watching this video. much love brother from Uganda.

@selenediorr 2 жыл бұрын

I spent an hour googling and I only watched your video for 2 mins and fixed the problem ✨🎉 THANK YOU

@Mburgdorf1 4 жыл бұрын

I think this is probably the best Excel training video I have ever watched in my life (and I've watched a LOT of them). Thank you for the spreadsheet download to follow along, for being so clear and for highlighting everything you do (and for the added "Anchor" and "Tab into" tips). You are a great teacher! This helped me understand so much better!

@ExcelCampus 4 жыл бұрын

Wow, thanks for your feedback Margaret! 😊

@beckychung3032 Жыл бұрын

I completely agree I've watched so many excel tutorials and never got it. I watched this the first time and got it right away. The worksheet to practice and follow along helped a lot! Thank you so much! Hitting the subscription button immediately!

@lanista78 3 жыл бұрын

I must say this is the best index match explanation I've seen. Seriously. Thank you.

@garrytalaroc 3 жыл бұрын

yeahh, other youtuber just doesnt work for me

@sweza2334 3 жыл бұрын

I have never come across any excel tutorial KZfaq video that is as informative and easy to understand as yours. BIG THANK YOU for sharing all these VALUABLE information to the viewers. I've always struggled to understand these formulas as I never learnt them in my uni, but I need to use them at workplace. THANKS AGAIN!

@joshuatatro4503 4 жыл бұрын

Much appreciated - I used to utilize Index/Match frequently, but it had been a while and I was making a mistake (forgot to anchor...). This explanation/tutorial is waaaayyyyy better than whatever I read/watched several years back, you're a rockstar.

@cathykoalapan 2 жыл бұрын

The best and most explicit tutorial of Excel that I have found so far. A lot of thumbs up! Thank you so very much Jon.

3 жыл бұрын

I've watched several tutorials before this one and couldn't understand how this works. The cheat sheet helped a lot. This is the best tutorial I came across and managed to make my formula work. Thanks, Jon!

@mleanot 3 жыл бұрын

I feel more and more like a magician every time I watch your tutorials. Great content!

@xiaoyantok7874 2 жыл бұрын

OMG. You are a life saver. I was struggling to understand what is INDEX MATCH and I’ve read notes and watch other videos, I still don’t get it. Thanks for breaking it down to such bite size, it’s quite idiot proof. I have just subscribed to this channel! Thank you!

@suegotcreative Жыл бұрын

Excellent! I remember the first time I saw INDEX/MATCH formulas - was totally blown away with the power of this formula. Was used to bring back consumption costs - electricity/renewable energies sector.

@sus8e462 2 жыл бұрын

Was having issues with getting VLookup to work, and this option for Index/Match was perfect! Using it to create a table to calculate price for leather goods--and using a table with leather name & price and putting into a formula to calculate cost for each item configuration (up to 6 pieces and 6 types of leather per an item...).

@pin3appl3man Жыл бұрын

I've gone to Exceljet so many times since I don't use INDEX/MATCH frequently, but you just explained it in a way that I'll never forget. Thank you so much!

@lilminishlomit 4 жыл бұрын

I've never heard of INDEX MATCH formula, until recently. But only with your video I can understand how to use it and write formula correctly! you explained it very clearly and easy to understand. Thank you very much!! I appreciate your generosity sharing these knowledge. Thank you for sharing the Excel file as well. I have liked and Subs as well.

@hc6143 2 жыл бұрын

I know I've commented this video before, but again, thank you so much for your Excel videos! Your work has been a significant help to me. Thank you so much!!!!!

@mushtaquejaffar7892 Жыл бұрын

I've watched many videos of excel, But this guy teaching way is so amazing The way he explain excel function/formula is so helpful

@ExcelCampus Жыл бұрын

Happy to help, MushtaQue! 😀

@sanjayshekar91 4 жыл бұрын

Awesome Jon !!! The way of your teaching is simple and to the point, Thanks for helping me out .

@hedonisticzen 3 жыл бұрын

Jon I love your teaching style, mentioning all the hotkey shortcuts and going through each portion of functions along with a file to follow along. Perfect!!!

@sedghmmer6447 2 жыл бұрын

Absolute ledgend, i'm currently studying online and they have privided us with the slideshows of the lesson but no one to present them, actual explanations help immensely, Thank You!

@accountebu 3 жыл бұрын

I couldn't get index match for years, I finally understood it and actually used it by watching your video. This is one of the best I have seen on how this index match works. Thank you so much!!!

@calinto1010 Жыл бұрын

I was lost till i saw this video, can't be explained any clearer. Thanks very much Jon, you are very good

@mennaeltoukhy8829 4 жыл бұрын

after a week, you are the only one I got the index& match function with data validation from,,BIG THANKS

@willkswill 4 жыл бұрын

Perfect!!!! Perfect!!!! Perfect!!! I was struggling with finding the perfect document to work with, and this INDEX/MATCH KZfaq video appeared.I am now able to understand this content very clearly, now I know that my students will also feel confident once I share the INDEX/MATCH examples with them.

@tawhidulislam3440 4 жыл бұрын

Hi Karen! You liked the video?

@marymarsh6300 3 жыл бұрын

Hey Jon. Thanks so much for this. Simple, professional, and very helpful. I'm using it to bring on sales folks, who have specified salaries over time, into a cash flow forecast that runs over time. As certain triggers are achieved in the cash flow over time, I'm adding sales folks and referencing their salaries looking left into the first month of their employment to reflect their first month salary in, for example, month 8 of the cash flow forecast.

@adelsaied17 3 ай бұрын

Well organized and explained, you really brought us on why Index&Match is better. highly appreciated.

@ExcelCampus 3 ай бұрын

Glad you liked it 😀

@slowhand4897 2 жыл бұрын

This is the only video where I understood how to use index + match instead VLOOKUP. Thank you sooooo much 🤩

@rizqomarrafatti6803 4 жыл бұрын

Watched this video. Really love how it eliminate the needs of changing column number by vlookup. But when working with really long formula, index match could be a bit confusing. And eventually i found the perfect option for me VLOOKUP MATCH Its very useful for me, hope others can finf it usefull too. But not forget to thank to creator of this video and chanel !

@mariastefanopoulos4068 3 жыл бұрын

Wow amazeballs!! You showed me VLookup and now you've shown me Index Match. Thank you very much.

@madisonthompkins9380 3 жыл бұрын

By minute 4 I learned 3 different shortcuts that I have never heard of before. Super clear presentation, awesome video. Thank you.

@bman342a 2 жыл бұрын

That's so funny, same here (I knew F4 but always forget about it)

@bkerr89 3 жыл бұрын

This video changed my life.

@avgvstvs8 3 жыл бұрын

I'd been struggling for so long until I found this video. You're a life saver! Thanks!

@recipehacker9752 3 жыл бұрын

Best video on the subject- PERIOD. I’ve seen them all, too.

@krizianoya1426 3 жыл бұрын

i literally solved my excel / google sheet dilemma with the help of this tutorial! index + match! such a big help. thank you Jon for your demonstration, will surely watch your other videos! God bless you.

@karinathich2268 3 жыл бұрын

Thank you for such a well-articulated video on Index match. I always thought index match was so hard, but you made is so simple to understand.

@TheFatController. 3 жыл бұрын

Thanks buddy. You just saved me about 5 hours of work. I had 58,000 rows of data with about 200 columns each from which to extract data, this technique did exactly what I wanted.

@marvinplacino6912 3 жыл бұрын

Hi Jon!!! One grateful new subscriber here 😊. I have been looking on how to use this same formula so I have watched multiple videos here in youtube and you are the only one who helped me the most in understanding how this function works . Please do not stop making videos like this because you are one of a kind when it comes to making the viewer understand what they wanted to know . Thanks and have a great day !

@samanthatan2313 Жыл бұрын

Thank you so much! Your video was really helpful. I can't believe i just learned how to use index and match in under 20 mins!

@xernonymous 2 жыл бұрын

Thank you for breaking this down in digestible bits. I work a lot with Excel in a Sales environment with large data sets. This tutorial definitely helped make more sense of what I'm trying to do with the data.

@93FOBINHOOD 2 жыл бұрын

You saved my life. I had the hardest time trying to figure it out before. Thanks

@syrah412 Жыл бұрын

Awesome explanation; I’ve done some long long formulas but this helped me avoid having to test/reverse engineer what these are doing. Well done; great video. Thanks for spending the time to educate the masses!

@dsquare7723 4 жыл бұрын

Thank you for the video. I plan to use the Index & Match functions you just described in an Excel time sheet along with a Table where the list of names continues to grow. Thank you again!

@joshuamanampiu6489 3 жыл бұрын

Finally I get to understand Index and Match after a long struggle. Thank you so much.

@affendibinrawi6941 Жыл бұрын

Seen many Index/Match tutorials, this is the best. Thank you Jon

@tawhidulislam3440 4 жыл бұрын

You are the best 💚 That is called perfect teaching! God bless you.

@vanessasmith7703 2 жыл бұрын

TY, TY, TY. I'v been trying to wean myself off V & H lookups but I'm having the hardest time grasping how Index Match works. Your video was extremely methodical and easy to comprehend. 🤞🤞🤞Let me go try it

@michellelatief2346 2 жыл бұрын

Thank you so much!!!! Your explanation is very neat and clear !

@luciahoyos9635 3 жыл бұрын

Love how you explain your videos, You really have a genuine passion for this! Big thank you

@edilpoulina1870 4 жыл бұрын

Hi Jon, Thanks for the explanations. I use Index Match amongst others to look up arrears in payments (30, 60, 90, 120+ days) by client.

@mimimgl2557 3 жыл бұрын

Omg, thank you so much 🙏. Never understood something complex so easily before.

@mahansor8295 4 жыл бұрын

Thanks Jon, I've seen sometimes can't get the right answer from vlookup and it's choice , just have to use index and match . Thanks again for this 👍

@wayneedmondson1065 4 жыл бұрын

Hi Jon.. VLOOKUP is convenient in certain situations, but INDEX and MATCH rules for all the reasons you outlined. Thanks for the comprehensive and thorough video, discussion and examples. Anyone not previously understanding the benefit of INDEX and MATCH should be able to put them to use after viewing your tutorial. Thanks for sharing and Thumbs up!!

@aimisayuti7894 Жыл бұрын

Thank you so much! This video solved the problem that I have been working on for 2 days toggling with vlookup function . Silly me. Very helpful and easy to follow.

@ashleysaffluence 3 жыл бұрын

OMG! This was amazing! thank you!!!! I was able to create a grocery list that includes the INDEX MATCH. way more easy to understand than my excel class I took a few years ago lol

@abuuthmaan1982 4 жыл бұрын

This is top dollar! Cheers bud! So much more flexible with being able to look left and not worry about structural changes to the worksheet. Thanks Jon. Keep up the fantastic work. UK.

@thal5835 Жыл бұрын

VERY WELL done video! I must admit, the INDEX/MATCH was confusing me at first, but this video helped clear up everything! 5-stars!

@jayp8186 3 жыл бұрын

You are best Teacher. I now clearly understand what Index Match Function Does. Thanks a Ton.

@jimmulvihillpmp5004 Жыл бұрын

Very professional and well created/produced. The circling of the array or individual cell is a nice effect and visual. Compared to some "how to" videos where the person records themselves on a cell phone this is top notch.

@ExcelCampus Жыл бұрын

Thanks, Jim! :)

@tjiss7094 2 жыл бұрын

I can’t thank you enough this has helped me analyse quickly thousands of rows of data in multiple sheets. Subscriber gained.

@monikab.6540 4 жыл бұрын

I really appreciate how clearly you demonstrate. Thank you!

@javedahmed8343 2 жыл бұрын

It is no exaggeration that I got my concepts clear of combine of two function. Thanks

@abdelazizZ Жыл бұрын

Best explanation on youtube for INDEX MATCH

@ExcelCampus Жыл бұрын

Thank you, Abdelaziz! 😀

@patelbrassturnomaticspvtlt5022 4 жыл бұрын

The best explanation from among hundreds of Excel Masters.... I just love the way you explained... It was awesome... I just subscribed and also refer many of my friends..... Great Work - Jon..... Wonderful... Keep it up.....

@hakankurt5142 3 жыл бұрын

Your tutorials are invaluable resource for me. You are doing such an amazing job!! Thank you for bringing up such good content.

@alkaamin660 4 жыл бұрын

Thank you. Easy to understand and follow the logic. I will repeat several times and practice with download work boo.

@darcyg852 3 жыл бұрын

You're an awesome instructor - I am grasping your tutorials more than I have others. Thank you!

@MsKaylee3 Жыл бұрын

You are So AWESOME !!! In one video you explained the most important and practical stuff!! THANK YOU!!!

@ricardoramirez8090 4 жыл бұрын

thank you so much for this awesome video. really helped me at work with a massive file with over 8,000 line items. Helped me arrange my data neatly without compromising accuracy. really made me quite the hero at work today thank you so much.

@philipraygibbons5600 2 жыл бұрын

This is amazing. I was trying to use two methods in a worksheet. I had vlookup/data validation to return estimated times on operations in a table that gets rows added. I also had those values with defined names so I could do formulas. The problem was that since I was sorting when adding rows to make sure my vlookup worked, I was messing up my defined names, as they are fixed. Super help for me. Now I dont have to sort the table and my defined names should stop getting broken. :D

@clintonmelusi9356 Жыл бұрын

since I started following you, Jon. I have become the best instructor thank you for your training technics Jon.

@thejordanadventures9083 2 жыл бұрын

This was a great video, made so much sense. I love how you broke down the formulas with different words of what is needed!

@lawrencesinger8320 3 жыл бұрын

Thanks for the great in-depth coverage of this topic. Now I'll see if I can use for populating an inventory template with inventory scans.

@myworldofbranchyz8895 4 жыл бұрын

This is a great tutorial! I really enjoyed it and I appreciate the effort to make it.

@ashishmittalLinux-freak 4 жыл бұрын

Bullseye, Simple, Crisp, TothePoint. You Rock Jon - keep up the good work

@Mnogojazyk 3 жыл бұрын

Excellent! In a matter of an hour or so, this helped me out of a jam that would have taken several days to do manually. Many thanks!

@hamzachoukair1459 4 жыл бұрын

Thank you for this! Honestly You make Excel sound like some fun game!

@nano7219 4 жыл бұрын

Excellent video, really thorough and easy to follow with the material provided. Plan to use it in a workbook that indicates provinces, counties, districts. Thanks a lot!!!

@stephencastro9148 2 жыл бұрын

Good video. Was having trouble understanding the function after reading a bunch of different sites but was able to use index match after watching the video. Thanks!

@equiwave80 2 ай бұрын

Hi Jon, I was scared of the INDEX & MATCH function but after practicing the concepts you have covered in this video I feel more confident. Thanks for all your efforts!!! Regards, Deepak.

@keithwhite2171 Жыл бұрын

Great video! I have always used VLOOKUP and have implemented work-arounds for looking left or whatever. You have prompted me to revisit INDEX-MATCH. Thanks!

@Vinson-Tran Жыл бұрын

Thanks homie. Just did my girl's hw question that she was stuck on now I think she gonna gimme toppy and zelle me money later for it. Preciate you boss man Jon

@AdirraBG 4 жыл бұрын

Very useful tutorial - concise and clear. I am going to start using Index Match function as it is indeed more flexible than vlookup in some scenarios. Thanks!

@venkatiyer7459 3 жыл бұрын

Thanks Sir ... time and again I watch this Video as Dictionary for my Index and Match solutions... Thanks again Sir

@simoiyahector-morales3781 4 жыл бұрын

Absolutely LOVE this tutorial, EASY to understand. Thank you sir VERY MUCH

@hannaearlrobit8927 2 жыл бұрын

Ive been looking for this for so long.. thank you very much

@KC-sm8if 3 жыл бұрын

after 2 years, this is still helpful

@annaelise207 9 ай бұрын

Hi Jon, I'm taking an intro to accounting spreadsheets course online, and it has been kicking my butt. We only have one video lecture a week and it is about 20 minutes, and doesn't nearly explain the information like you do. Thank you for taking the time to create these videos for us, it has already taught me a lot and made these nested functions make sense. :)

@ExcelCampus 9 ай бұрын

Thanks for the feedback, Anna! 😀