The Lookup Formulas Training Series Part 3:

## How to Use INDEX & MATCH Instead of VLOOKUP

Download the file used in this video:

INDEX MATCH Explained.xlsx (128.2 KB)

## Lookup Formulas Training Series

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!

Difficult to answer your question at this point. I am just preparing for an upcoming position. I’m not sure what I will need to know.

I just know these are common functions.

Suggestion re: your notes – I’m wondering if some how you could put more of your examples on one worksheet – so not so much paper is wasted in the printing process?

Looking to the Left which VLookUp cannot perform.

Very easy to understand once explained by you. The INDEX & MATCH formula lookes super complex, and this is why I used the VLOOKUP. However, after this tutorial, I am using the INDEX&MATCH to avoid errors.

Thank you so much for sharing the knowledge.

Thanks a lot sir for this new learnings. I preper to use index & match rather than vlookup.

Thanks for INDEX + MATCH function. It is very useful for me.

Errors mostly are #N/A and #VALUE……As far as INDEX & MATCH go…..after watching your video, I think I will be using them as a preference now.

I’ll be using this just for learning at the moment. As I said in my first comment Jon…..I only know the basics and it is very frustrating to me because Excel is such a powerful tool…..

Keep up the good work Jon……your videos are helping a lot of people.

Just for learning and I really learn from you. Thank you!

Great!!

Clear to the point explanations from Part 1 -3

Thanks!

Great breakdown of using INDEX & MATCH together versus VLOOKUP!

Hi

Jon !

Now I clearly understand Vlookup cannot Left value eg -1 column . And also understand Index and match simply definitions.

Thank you Teacher.

Excellent descriptions which are very easy to understand. I love Jon’s teaching style nothing hiding like the others video. Thanks a lot.

Jon, another great video (as always). Based on this video I replaced VLOOKUP with INDEX/MATCH for monthly reporting of patient satisfaction scores (I work at a healthcare system). It will reduce the data cleanup time since:

The report is provided by the vendor and has the potential for changing on occasion. Typically columns are added or removed. Hmmm, isn’t that the example you use in your video for when VLOOKUP could break? 🙂

I was also reminded that this will allow me to ‘look left’. I have other automated reports provided to me by vendors and/or other departments and some of my first steps are moving columns around so that my VLOOKUP formulas will work. Well, not any more. I am excited to change many, many reports where my days of manual column shifting will be a thing of the past.

Thanks again!!

Eric

In lieu of the more fragle Vlookup

This is amazing just what I was looking for. I have data from two sources and I wanted to make sure that the data was the same in both sources this has solved the problem for me saving me hours of coping, cutting & pasting and manually comparing the data.

Brilliant thanks Jon. Definitely signing up for more courses.

I definitely agree 100%. I also noticed you mentioned ‘comparing data’. I am unsure of the amount of data you are comparing, but I am embarrassed to say that I have been an Excel user for 22+ years and have just started using the wonderful ‘EXACT’ function. Somebody mentioned it to me and it has saved me a ton of time. It is a ‘simple’ function, but better than trying to compare data on a screen after staring at a computer monitor for hours at a time.

Again, I use it depending on the amount of data I am comparing, but it may not meet your needs or you may already be aware of it.

Take care.

Can’t see me needing to use this at all.

Beautiful explanation with examples.

I will use it for extraction Sales of customers based on month.

2 way row and column.

Hello Sir, Thank You, Step By step in details, with easy understanding language, appreciated, expected more videos, thanks a Lot Sir,

This has been the best tutorial for “INDEX/MATCH” I have found. I have a much better understanding of the functions.

Thanks so much Lindsey! I really appreciate the nice feedback. 🙂

Hello Jon, thanks very much for part 3 Video I have learnt that there are some alternatives for v-lookup functions and reasones why better to use something else sometimes

The lesson was so awesome with this support I can see myself an expert in excel in the near future

The presentation was clear and friendly with simple English

I use Index-Match function now more than I use VLookup, for reasons explained in the video. I am looking forward to the new course out soon so I can gain more insight to think about application for my role as a compensation analyst in human resources departments.

GREAT !!

WELL EXPLAINED, THANKS

I like this concept but have a question:

Many times when I use a vlookup, I will need to copy it over to pull in multiple columns of data using the same lookup value (Example: using a customer number as the lookup value in a sales table and pulling in customer Address, City and State from a customer table). If I anchor both the lookup value and the table array, it is simple for me to copy the formula into the next column(s) and simply update the column index number I wish to pull in and then copy all of the formulas down my worksheet.

Is there a simple way to copy/paste an Index/Match formula to do something similar to the above example or does the formula need to be rewritten “from scratch” for each piece of data I wish to pull over?

Index match is more useful than vlookup because it will work even if the worksheet layout is changed by either inserting or deleting columns.

Thank you Jon! You have an awesome teaching style and gave a great deal of value in these three videos. This is definitely proof positive that your courses will be over the top in providing much needed understanding of Excel’s most used “finding” functions. Really appreciate the effort you put into these instruction videos. I’d say there is no MATCH for your LOOKUP training! Sorry, couldn’t resist…

Hi Gayle,

LOL! I love it!

Thank you so much for the nice feedback. I’m happy to hear you enjoyed the training and are learning more about Excel.

Thanks again and have a nice weekend! 🙂

Gayle – I like your sense of humor.

Thanks Jon, thanks a lot for these helpful lessons. I really learn a lot from your lessons and your teaching style is very effective. Please keep posting these helpful lessons.

John

Enjoyed the short video on Lookup and Match. Really enjoyed the simple-type approach. Thanks.

Awesome stuff

Thank you. This is a great start to developing my knowledge of Excel.

John,

In the above video, you had also anchored the Range in the first argument of the INDEX Function ie $D$4:$D$8

in INDEX($D$4:$D$8,MATCH(B12,$B$4:$B$8,0)). By “anchored” I assumed the range should not change even when you insert columns in between. But I noticed that as soon as you insert columns for example between Columns “C” and “D” the first argument range of the INDEX function changes to $E$4:$E$8. So strictly speaking this Range is not an anchored Range. Am I right?

I have a table that includes countries, 2-letter ISO assignments, 3-letter ISO assignments, number assignments, etc. I will be using index and match rather than vlookup to get the return values that I need because I am often adding columns to the data for a host of reasons and it messes with the formulas I write.

Thank you for providing such a quality video tutorial. It is obvious that you take the time to make your training very clear and polished and have the foresight to wrap the training in a down-to-earth context making it easier to retain and grasp. It speaks volumes.

You are very much appreciated my friend. Keep up the good work!

I will use index and match formulas to analyze data I export in table format from SAP.

Hi Jon.

I sincerely appreciate the tuit,I truly believe in some few upcoming days I will master V Lookup & Indec-Match.

Be blessed Jon this has been a golden opportunity for me to drain wide knowledge in excel.

Long live John.

Great video Jon, I would probably use the index and match function to bring back information I need from data that I export to Excel from Access. I do a lot of work with databases so this would be very helpful and also won’t limit my searches as much as VLookup does.

Hi Jon, love the videos, thanks so much..I wish you showed an example of INDEX & MATCH referencing columns to the left, in other words a “-1” in the column criteria. I will use INDEX & MATCH to lookup data in a very wide data set with lots of columns so that I could match rows and match columns simultaneously to make the formula more dynamic than a VLOOKUP.

Hi Jon, thanks for this third part of the trilogy. Very nicely and clearly explained, as usual.

I’m a big user of the VLOOKUP and indeed had always to use tricks to make the formula working all the time (like having dummy duplicate columns or searching for the column’s name instead of using its absolute number in the range).

I never met excel teacher like you before and you are also very expert in excel and your teaching style is very effective.

Very well closed. I have enjoyed the session and have learned something new that will be helpful in my routine job. Thankyou very much for such an wonderful class.