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!
Thanks Jon,
The videos are really easy to understand and very helpful. I am changing from VLOOKUP to INDEX, MATCH to give me more flexibility.
Hi Jon,
Thanks for this series of videos. I have found them very useful. As a somewhat dedicated VLOOKUP user I was a little suspicious at first. Thanks to you I have a very viable and useful alternative.
Many thanks,
JP
I’ll use INDEX|MATCH for producing a list combining lab technicians and the methods each lab technician is approved for. this method is better as there ongoing is replacement of staff technicians and the methods used by the individual lab technician approved for.
Thanks Jon
It’s really useful video. I would be happy to see some more videos. I’ll really help all of us in our day to day work.
Great job.
Suresh rai
The training video is very helpful in differentiating between Vlookup and Index&Match functions and their purpose. As you requested us to write a comment on “What will you use Index & Match instead of VLookUp?”. I use Index & Match function for the following two purposes:
1. VLookUp can’t return a value on the left side of the intended cell. rather it always looks to the right side by doing the criteria on the first column.
2. VLookUp is not flexible. this is because when a new row or column is inserted, it returns zero value: since it is working by indexing the either the column or row using numbers.
Hi Jon
Thank you again for this useful video and the file. I have used VLookUp on numerous occasions and find they work perfectly well but my data has never changed, so the formula has never fallen down.
I could now perhaps use the Index/Match feature if I know that there is a possibility my data will change in order to avoid then having to amend the formula. I must admit, it seems a rather convoluted way of performing calculations and I’m hoping Microsoft eventually think of a way of simply using a VLookUp but with more flexibility. For instance, the ability to have the cell value in another column as opposed to it always having to be in the left-most column and letting the user insert/delete columns from the lookup table itself and the formula still works.
Thanks again.
Louise
Hi Louise,
Great suggestion! Yes, I’m hoping that function will be available someday too. 🙂
This is a great explanation of INDEX/MATCH. I will now use it more often than VLOOKUP as I compare data from multiple sources.
Will use Index and Match instead of VLookups where I am not sure of the layout of my data and columns still needs to be added or deleted. Index & Match is more flexible.
thank for the useful video
I never knew that “INDEX & MATCH” combination was this simple to use and of coz….really useful too. Thanks a lot Jon!
Dear Mr Jon,
I’m so happy today, because in my free time at office, I did try to use the index and match from your video. And, It’s so great. I was successful. I used it rightnow in my work. It’s so heplful.
The more you send me the video, the more you get the appreciation from me.
Thank you for your support Mr.Jon
We have Joint Ventures for which I must allocate income and expenses to 100+ investors. The data is from Microsoft GP exports and the income and expense accounts can vary year to year. INDEX & MATCH will allow for such changes without have to “re-invent the wheel” each year.
I already use the INDEX MATCH function, but understanding the structure is very helpful.
You explained this very well!
The comment about the [] in a function being optional was something new I learnt.
Thanks!
James
Will the Index/Match function work with pivot tables? As I refresh my pivot and bring in more categories the Total column moves out. If I need to pick up that total, the Index range would need to update accordingly.
Hi Ken,
Great question! No, the Index array will not automatically extend with the pivot table. You would have to create a dynamic named range for the pivot table, and reference that for the arrays in the INDEX and MATCH functions. I don’t have an article on that but I will add one in the future. Thanks!
Another alternative is to use the GETPIVOTDATA function. I explain more about this in The Ultimate Lookup Formulas Course. Thanks!
Thanks John
This INDEX – MATCH method solves the problem so often overlooked by us amateurs when we inadvertently add columns to the table where the VLOOKUP has its values
Does the INDEX-MATCH combo break when one adds a row in between the range?
Hi Norman,
Thank you for the nice feedback. No, INDEX-MATCH will not break when you add rows within the arrays for the INDEX and MATCH functions. The references in the formula will change to include the new rows. If you add a row below the last row, the range will not update. So it’s best to Insert rows between the first and last row of the range.
Dear Sir,
Supper
thank for the useful video
Thank you for making Excel so easy and fun to learn!
The Index Match will solve a lot of problems. Thanks Jon!
Please update my email because these are a great series!
Thanks for the great info. I can use it to merge census data contained in separate worksheets.
Hi Jon – I must say that this is the best video tutorial I’ve seen.. Thank you for uploading this!
Hi Jon, your tutorials help me a lot for quick recapitulations. Keep up the great work of posting such precise and crisp tutorials. You would not know how much they help for people like me looking for concise referrals.
Thanks!
Thanks Jon,
The error I got most is putting incorrect array. I was mistaken entering array covering lookup_value column and lookup_array altogether. Your presentation explained step by step and it was easy to follow and understand.
Is there a format we can put array covering all columns, in case we do not know which column number to match. I’m unsure if my question is legit.
Thanks Jon,
Videos are very helpful.
Hi Jon,
Thanks a lot it is incredible the way you explain the functions, so clear and strait forward!! Keepup the good work.
ST
I am just a beginner so don’t know.
Hi, all is fine thank, but how i can know the price is yo want another type, i mean instead grande i want tall or venti.
thanks so much.
Hi Jon,
It was a good lesson. I like your explanation.
Thank you very much
Best video tutorial
I cannot think of where I would use Index and Match lookups yet. But knowing about it will be nice in case I need it.
This will enable me to use a single column and retrieve data from the left instead of duplicating the (key) in the left most column, excellent, I’ve thought there must be a methed and now I know.
Many thanks
You have made what is a difficult formula to understand, very easy to follow. I will certainly use index and match now. Thanks very much Jon A 🙂
The way u explain is really easy to understand.
Thank you Excel Guru.
Thank you, I have needed this formula so many times and it seemed too confusing to me, not any more. You explained it very clearly and I will use this as my ‘go to’ formula for lookups from now on. I use Vlookup all the time but too many times I have to move an entire column in order to use Vlookup (because vlookup doesn’t look to the left). Index & Match will save me a lot of time and effort. Thanks!!! I look forward to your next video. Very professional, by the way.
Thanks Jon.
Very good videos and excellent explanation, easy to understand.
Very Easy to understand video. Great Job. Thanks a LOT.
Hi,
Thanks Jon. I would be using this for my company internal cost structuring analysis that I was recently assigned to.
BTW, I am sure there are some videos you done on Pivot tables. Could you direct me to those? If possible.
Thanks you
Hi Jon,
I must say that this is the best video tutorial I’ve seen.. Thank you for uploading this!
Hi Jon,
These will help us in sizing the circuit breakers, conductors and conduits in a feeder or branch circuits. We used tables from the electrical code to do this and with these Excel functions we can automate and avoid any mistake in determining these items.
Thanks for the videos.
Pepito
Plan to use Index/Match +INDIRECT to look up data from large pivot tables where the range is dynamic.
Hi Jon,
At last! I now understand that I could have used index/Match formulas instead of lookups and saved hours of formula rewriting all because the recipient of my spreadsheet wanted additional columns. The risks involved in returning the wrong information was high and therefore involved not only the changes but lots of time checking the results. On a large sheet with critical data this was an unnecessary nightmare! Over the years I generally have only learnt specifics about Excel that were pertinent to my task in hand and wish I had known about your training videos years ago – How long have you been going? So far I have been impressed with the clear presentation and ease at which these sessions help to understand Excel better. Well Done – I look forward to other sessions that may be useful to me.
Dear John,
Lot of thank for the video. Please include match function with multiple criteria.
Very easy to follow, thanks for the examples for practice. Great job!!!
Thanks Jhon for the wonderful video.
Further, please provide us a video of match function with multiple criteria.
thanks a lot.
i always wanted to know the match index formula but i always think that it is really complicate.so i afraid of this formula.but with this video i really undrestand and love it.
was not aware of match and index but I can see how it can be a valuable option.
Thanks for these videos, I am just loving it because its so clear and easy to follow the teachings. At the moment I am not working with Excel in the workplace but just learning to use it in the near future. Thanks once again for all the amazing videos!
Thank you for this explanation. I use vLookup often and always wondered why my formula broke! Now, I clearly understand the impact of inserting a new row or column into the source data. Going to modify those formulas that are vulnerable. Thanks!
Your videos are so easy to understand. As if, I’m just in a class. I’ve learnt VLOOKUP & pivot table from your videos. Thanks a lot Jon.
Moushumi