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!

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.

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

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

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.

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.

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.

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

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!

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?

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

Awesome stuff

John

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

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.

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.

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

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?

GREAT !!

WELL EXPLAINED, THANKS

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.

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

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

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 Sir, Thank You, Step By step in details, with easy understanding language, appreciated, expected more videos, thanks a Lot Sir,

Beautiful explanation with examples.

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

2 way row and column.

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

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.

In lieu of the more fragle Vlookup

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

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

Hi

Jon !

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

Thank you Teacher.

Great breakdown of using INDEX & MATCH together versus VLOOKUP!

Great!!

Clear to the point explanations from Part 1 -3

Thanks!

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

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.

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

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

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.

Looking to the Left which VLookUp cannot perform.

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?

I use VLOOKUP to pull data from worksheets in a workbook and populate our monthly and YTD financial statements. I was hoping Index/Match might help make my worksheet more flexible and dynamic but alas, no. I don’t want to specify the column to look in, I want Excel to find the correct column, such as December 2019 or January 2020 and return the value. Currently, I have to change the column number every month in my formula to index to the next month. For example, next month I need column 8 which is January data: =VLOOKUP($B7,BudgNonRA!$A$7:$M$87,7,FALSE). Can it match the date on my financial statement to the column date in the table array? For example, find the column titled “Dec-2019” or “Jan-2020” instead of the column number?

I would use Index & Match instead of VLookup, because I can add columns in my spreadsheet without it breaking my formulas. VLookup formula is broken when I add columns in my report.

I prefer VLookup when pulling data onto tables

I need to play with both options a bit before I decide. Thanks!

Great learning experience. I work with worksheets and workbooks to extract data. (typically data on 1,000+ students). I got lots of little pearls of wisdom watching your YouTube videos and these three videos with worksheets. I find that VLOOKUP is the best/easiest way in my situation but I do one final step – remove the formulas once I’m sure any “Errors” are corrected.

I Copy the column with the formulas – then paste it right back as VALUES. Why – because if the Workbooks or worksheets get moved, deleted or not available due to “Rights” issues. – all the formulas fall apart. (Instant headache when you see 1,000 Errors or N/A’s. ;-(

Quick question: What is the reason for not using the columns for the array (i.e., $D:$F) – I understand if you have other data in the same column that you don’t want. Just wondering.

Suggestion: Might be helpful to include a column with the formulas being visible [=FORMULATEXT( )] you discuss in the Errors worksheet. It makes it a lot easier to understand your solutions (THEY ARE A BIG HELP TO ME).

Jon. Estudar em Moçambique é complicado, muito complicado mas com estas extraordinárias lições meu Deus, lima tudo e todo defeito que nos temos sobre estes pacotes. Hoje tenho melhor visibilidades dos comandos fácil aprendidos em seus vídeos. Obrigado pela rica partilha de conhecimentos.