The Lookup Formulas Training Series Part 3:
How to Use INDEX & MATCH Instead of VLOOKUP
Download the file used in this video:
Lookup Formulas Training Series
Video 1
Intro to VLOOKUP:The 5 Must Know Essentials
Video 2
Lookup Formula Errors:How to Prevent Critical Errors
Video 3
INDEX & MATCH:An Alternative to VLOOKUP
Which Error Do You Get Most Often?
Please leave a comment below with your answer, and any questions. Thanks!
Hi Jon
Excellent! Thoroughly enjoyed this video, with down to earth, plain, and simple explanations of how the formulas work.
Great course.
Thank you David! I’m happy to hear you enjoyed the video.
Very high quality explanation.What can I say?-Awesome
A very strong applause.
Nice video, step by step discussed.
INDEX & MATCH is important for doing left side lookup which is not possible for VLOOKUP function. Even LOOKUP function also can do left lookup but with certain restrictions of its own. Thanks for sharing, Jon.
Great point! Thanks Rajesh!
Awesome.
Keep the videos coming. Very simple and easy to grasp explanations.
Wonderful explanation ! I’ve taken a course with these concepts before and it really didn’t sink in. Jon’s clear instructions made the difference. Now I can see the power! I will never use vLookup again!
This will really make it easy to search our hardware inventory – sometimes we know the serial #, sometimes just the model # or maybe just the location. Using Index and Match I don’t have to choose just one way to find the record.
Awesome! Thanks Diane! I am so happy to hear that you will be putting INDEX MATCH to good use in your files. You made my day. Have a good one! 🙂
Hi Jon,
Thanks for another good video. In video 2 he mentioned that by using the iferror function, excel would run quicker. How does that work? How can excel run quicker by adding more code?
Thanks
I didn’t know I was into INDEXING since I was a kid. I would always grabbed the mapbook and figure out where my dad was driving us. Little did I know I was ahead of myself. Now that I know what this is called, I’m much more confident in using INDEX/Match whenever I need it.
Nice going on this one! And I didn’t even know until now. WOW!!!
Hi Jon
Great tutorial on Index and Match. Never really considered using I & M before even though I had checked it out before. Definitely will from now on.
I’ve just done some examples using your downloadable file where i used the match function to lookup both the row number and column number to return a result for two data items. ie:-
=INDEX(A3:H11,MATCH(A16,$C$3:$C$11,0),MATCH(B15,A3:H3,0)) – this looks up the name to determine the row no. then looks up the column no. to determine the type of information being returned.
Both data items are data validated cells so they can be selected to show whatever.
I’ll be using this type of lookup in many of my spreadsheets from now on I imagine.
let me know if you’d like to see my example spreadsheet.
Cheers
Bruce
Jon. WOW – A Light Bulb Moment !!
Excellent video with an easy to understand step by step approach. I really like your style and content and I’m keen to practice and further understand what I’ve just learnt. INDEX and MATCH here I come!
I use Index/Match as a 2 way lookup, which you will probably be covering in your Advanced lookup course. I can specify the coffee type (Café Mocha) and the size (Venti) to return the correct lookup value.
I appreciated your presentation, as I am always looking for better presentation techniques and/or descriptive terminology in presenting Excel concepts. Besides I always get something from your training, being an Excel “expert” to me means acknowledging that “I do not know everything”. This is kind of oxymoronic, but Excel is too big, too wonderful, and too dangerous to refuse to learn more or learn “again”. Thanks
I really enjoy your style Jon … you are a great teacher. I believe I can use index and match much more effectively than my previous VLOOKUP formulas for my payroll spreadsheets. Thanks for sharing!
Excellent and well presented. Andy has helped me tremendously! 🙂
Spent many months trying to get my formulas to work with multiple arguments across different worksheets.
So much easier now with these better understood methods.
Thank you very much.
Great course! I’ve been using Index Match combos for a few years and learned a lot of tricks that can be done with it. Actually, I forgot about vlookup/hlookup because I use index/match by default for everything (especially since I’m prone to tinkering and moving things around a lot). This single thing in Excel is probably the single best thing I learned and I’m glad you have a course to share it with the world!
Thanks George! I really appreciate the great feedback and happy to hear you learned something from the course. 🙂
I use vlookup to analyse sales data but regularly had to make changes to the formula when adding and deleting cells which is necessary to line up our part numbers with customer’s part numbers. Now I’ll use Index & Match and I won’t have to continue to tinker with formula as I change the spreadsheet.
Also I often have to vlookup for data behind the lookup value which involves manual cut and paste of other columns. No longer!
That’s great Logan! I’m so happy to hear that your are using Index Match now, and eliminating some manual work. Awesome! Thanks for leaving a comment.
will allow me to retrieve info from a table without having to rearrange the columns. Index & Match can look to the left.
to automate a company”s spreadsheet
Since I have become familiar with the index-match combo, I rarely use vlookup to reference items anymore. Thanks for sharing this video
Awesome! Thanks Jay!
It will be helpful for my assignment, thanks a lot
Great Video Course,
I’ve been having issues with VLookup and did not really understand Index and Match. These two gems will solve my problems. I’m a newbie when it comes to the more intense formulas. I’ve used VLookup, and Count IF for years but this is a nice boon to some of the spreadsheets where I only have certain columns of information the must be matched. As you stated if I added a column (which I’ve done) then I had massive maintenance to complete. This will solve the issue. Good tutorial and the little cheats are nice!
Thank you Joy! The INDEX/MATCH combo can really save some time and headache. I’m happy to hear you will be using it in the future. Have a great day! 🙂
Thanks Jon. It is very clear to understand those 3 Functions.
For changing data were items are added or deleted. It seems that match-indexing supersedes vlook-up, unless you have static data, and even then match-indexing would still work and be more robust. Is there a situation where vlook-up would be better?
Hi Michael,
My apologies for not getting back to you sooner. You are right, in both cases Index Match is a better alternative. So why learn and use vlookup then? Well, vlookup is more popular. More people know how to use it, and therefore you will see it in a lot more workbooks than Index Match.
I always try to consider my audience or users before deciding which one to use. If I am going to pass the workbook on to others to use or maintain, then I want to consider if those users know how to use Index Match. Or if it is important enough to train them. If you are sending a file out to non-finance people (sales, marketing, etc.), then using a vlookup can be the difference between the user understanding it, or you getting a lot of calls if it contains more complex formulas.
The other advantage of vlookup is that it can be a bit quicker to type. Like you said, if you just have a static range and need a quick result, vlookup can work just fine.
I hope that helps. Let me know if you have any other questions. Thanks and have a nice weekend!
Mr. Jon, I am ever grateful to you for uploading this video. Your explanation is very simple to understand. Hats Off to your noble work. You are doing a great job for helping the computer illiterate (Excel) people to learn and creating an interest to learn the formulas and to improve their productivity in their day today computer Excel use. I am very eager to personally contact you over phone. I am residing in India and one of your most admired fan. Please speak to me for a minute. My cell no. is 91 9448846318. I want to learn many more things from you. Please post the excel formulas at the regular intervals.
Yours most admired and loving and affectionate.
gopalakrishnarao.
Hi Jon,
Thanks a lot for three upload videos… wonderful the way to explain. I need this videos to remember because I to use a lot in my work with big tables and differentes values to find. And my boss introduce me to Power Query… I hope to see comming soon a video step by step how to use and to do the match for new tables that user need.
Thank once again to share your knowledge
Rodney
Thanks Rodney! I have an entire module dedicated to using Power Query in my Ultimate Lookup Formulas Course. It is a great tool that can be used in place of lookup formulas in some situations.
Thanks again and have a great day! 🙂
Hi Jon,
Thanks a lot for the videos.They were very helpful and will definitely use them in my work. Looking forward for more videos from you.
Thanks,
Sylvia
Thanks Sylvia! Happy to hear the videos helped you. 🙂
Hi Jon,
Super, you took all the confusion out of Index and match for me.
Love your videos.
Thanks,
Julia
Thanks Julia! I’m so happy to hear that. Have a great weekend! 🙂
good lesson!
i can see possible uses already for grabbing information from a huge workbook and avoiding frustrating scrolls the get it.
Hello John,
This is very helpfull and very well and simple explained. I use it on my job for creating certain reports.
Thank you for sharing it with us!
That’s great to hear. Thanks for your support Aleksandra! 🙂
Thank you for the great tutorial and the clear explanations!
I too can see myself using I & M to overcome the limitations of VLOOKUP when it comes to looking to the left. I’ll definitely continue to practice with these super useful functions.
Thank you very much.
Awesome! Thanks Francesca. 🙂 Happy to hear you are learning and practicing Index & Match. It becomes easier to write the formulas with practice.
I enjoyed the video Jon. I do have a question. I have a project to complete on my job and it is a manual process. Do you have a video on ranking ties when there are multiple columns and the data may not be present in each category and it for month over month and year to date for the top and bottom 5 agents.
This will ope up the arsenal of ways to do things in excel. I like the logic behind your explanations. I love to know the why behind the things I do in excel. Thanks.
GREAT TUTORIAL THANKS.
Great!Thanks a lot.
Super and very useful
Hi Jon.
IN ONE WORD : NICE AND EASY
GREAT JOB !
Hi Jon,
In simple terms – Awesome…. and Thank you very much for the sessions.
Happy posting !
Cheers,
V
Thanks Jon, I am advanced in v-look up but never got a chance to look into index and match. Now in 15 min I learnt it perfectly.
Awesome! Thanks Mali! 🙂
Hi Jon,
Headrest thanks for sharing this video. Its gonna help my work more easier than before.
I will use it for complicated budget files.
Excellent step by step instructions in clear language.
Thanks
Thank you Gordon!
Hi Jon, Thanks again. I have never really used index and match because my data would never change in terms of column values. However, I can certainly see how this would be very useful. I almost think that if you got more complicated than that you may be better off using VBA. An example would be if you needed to do a lookup based on column headers – if a particular header exists then proceed otherwise return a static value like NA or “unavailable”.
Thanks again Bernice! I’ve seen index/match used for some extremely complicated formulas and scenarios. The solution you choose largely depends on who you users are and what they know. I try to keep my models as simple as possible so someone else can eventually maintain and update them. But index/match can be used in place of vlookup and there are some advantages. However, it’s easy to default to our old favorite (vlookup) and nothing wrong with that. 🙂
Nice Videos
Thanks..!!
I really enjoyed the video and learned a lot. Jon, you provided the best techniques to convey the information clearly to listeners.
thank you so much, very helpful videos
Thanks Mimi! 🙂
Thanks you very much for this video. I now have the answer for problems I could not easily resolve before.
Awesome! Thanks Mohamed! 🙂
Jon!
You have such a way an elegant way of communicating complex information. You are a born educator!
To further your example, if I wanted the user to be able to select both the “Classic Favorites” and the size. How would you recommend writing the formula?
Jan
Thank you so much Jan! I really appreciate that.
This is a great question! We can also use the Match function to lookup the size, and return the column number. In this case we will expand the array in the Index function to include the entire price table, C4:E8.
The Index function also has an optional column_num argument. In the example in the video we omitted this argument (left it blank) because we were only concerned about finding the matching row.
To specify the matching column we could add a column number for that argument. The formula would look like the following if we wanted to find the price for size Grande in column 2.
Notice that the array has been expanded to C4:E8 to include all cells that contain a price. And we are referencing 2 for the column number to return the price of the Grande size.
If we want to make this more dynamic we can replace the 2 with another Match function. This Match function will lookup the size that the user selects or inputs in another cell. Let’s say the size is in cell C16. Here is the formula that will lookup both the item and price.
That second Match function is going to lookup the size in row 3 of the sheet where the sizes are listed. It returns the column number from C3:E3. This is NOT the column number on the sheet. It’s the column number of the range C3:E3.
This formula can be used as a price calculator where the user can input/select the item (cell B14) and size (cell C16) to return the price.
I cover these types of calculator formulas in a lot more detail in The Ultimate Lookup Formulas Course, but hopefully that helps get you started. Please let me know if you have any questions. Thanks again Jan!
Thank you Jon!
It worked perfectly…of course.
I will be reducing the number of vlookups I use in complex spreadsheet in the future.
I’m happy to hear it. Thanks again Jan! 🙂
Hello Jon,
The Index Match video was another great one. I loved learning about these options but I am not sure where I will use them. My V lookups are in our monthly payroll entries and work well. I will certainly keep Index Match in my mind now that I know it exists. The entire 3 video series was really great. I appreciate the knowledge that you shared and your teaching method is top notch.
It was excellent and simple explanation about Vlookup & its alternative. We are expecting more from you sir,
Thank you
Thank you Manivannan!
Thanks – will try using this instead of vlookup – I think it will help as I’m having to name the range but this should help me to go straight to data in column I really need to check. Just need to get used to understanding this new way for me of looking up.
Thanks Antoniam! It takes some getting used to, but Index/Match is a great combo to learn.
Thank you Jon! These videos are really helping me a lot. Though, for the first time i comment on your video, but i certainly appreciate your help. Thanks again.
Thank you Farhad. I’m happy to hear it and appreciate your support! 🙂
Thanks Jon, this is great.