Stop using VLOOKUP in Excel. Switch to INDEX MATCH

preview_player
Показать описание
Tired of the limitations of Microsoft Excel VLOOKUP or the constant N/A errors? Within a few minutes you can rid VLOOKUP from your life. Switch to INDEX MATCH to end the tyranny of VLOOKUP forever.

In newer versions of Excel, you may find that XLOOKUP is a better alternative than INDEX MATCH but older versions of Excel and Google Sheets do not offer XLOOKUP as of 2022.

00:00 Intro
00:46 VLOOKUP flaws
02:34 VLOOKUP example
05:31 MATCH formula
07:09 INDEX formula
08:06 INDEX MATCH combined
09:36 Compare INDEX MATCH to VLOOKUP
10:38 Conclusion

Рекомендации по теме
Комментарии
Автор

This was SO MUCH BETTER than trying to make VLOOKUP work across multiple spreadsheets! THANK YOU! A year and a half later and this video is still helping! Ü

joshw
Автор

This is a really good explanation of INDEX MATCH, which for some reason I just could never get. Just a tip, if you only need the values and won't need them to change after you're done with your lookup, highlight the row, ctl+C then paste the row back into your spreadsheet as values (right click + paste 1-2-3). That way your formulas don't break and it won't slow down your spreadsheet.

celan
Автор

What a life saver! I've been battling for hours with VLOOKUP (even though I'm doing it EXACTLY the same way I usually do it), and INDEX MATCH worked like a charm across two tables in different sheets! Excellent video, thank you kindly!

WaRnb
Автор

I watched your video for a good alternative to VLOOKUP, but your VLOOKUP explanation was so simple that I actually understood your example and just ended up using that! Thank you!

KuroDensetsu
Автор

Thanks so much for making this video. Really helped a lot. I was just about to start duplicating my data to make VLOOKUP work and thankfully came across your awesome video. Much appreciated.

anyyoyo
Автор

This is quite excellent. To expand, I really need index match to return the contents of an entire row. EG, I have exported out of Sage 50 our entire inventory to csv. I have also exported all item ID's sold in the past 2 years. I need to create a new sheet that contains all data from the inventory export for each item sold in the last two years so I can build a new company file with only that data in it.

VonSpinx
Автор

great walkthrough. I love the way the video was organized and how you stepped through each part!

controlsgirl
Автор

Just few days ago, I searched youtube to find why my VLOOKUP formula returned #N/A. And the next one-or-two days, this video pop up in my YT Home. You mentioned the EXACT problem of VLOOKUP I had! which was the lookup value was not on the FIRST COLUMN of array! What's better, not only you gave me answer, but also a BETTER SOLUTION: The Index Match! I really thank you for this!

some comments did mention to use XLOOKUP, but I'm using excel 2019, the formula isn't yet there. so Index Match is the perfect answer to my VLOOKUP problem!

ddaarrkknneezz
Автор

Really helpful. VLOOKUP is indeed very useful but has its shortcomings as you mentioned. INDEX MATCH is easy to implement and understand.

priyaroop
Автор

INDEX MATCH formulas have been one of my go-to formulas for years. It can greatly improve productivity and even expand the range of one’s capabilities.

btbb
Автор

I did in fact stop using the vlookup long time back and switched to index - match. Just love it. So powerful.

shamsularefeen
Автор

Ugh! How are you literally the most exceptional human in the world?!?! This made my work so much easier!

sisasenkosimbambo
Автор

That's cool. I use Excel a lot and love to see other options. This one is very useful! Thanks for sharing. Good explanation, too.

jbraun
Автор

Soooo, this is the age old battle.. I am (or was) team vlookup over IM. a big reason is, Index Match is tougher to learn and the formulas can get confusing... FAST. This is not so important with deveopers and analysis, but in many cases, I am building a model that will be passed to some not so technical people to manager.. In my experience the could grasp the vlookup concept easier.

That being said, the critique is right one. V's biggest drawback is the lookup has to be in the left most column. It's true, but for me, I just made sure my data was structured that way to begin with (key on the left is good form anyway)..

The thing is, neither one is wrong and it's fantastic to have both options.. At least it was until XLookup came along and sent both of them packing!!

Good video thought ! really great examples and production.

thinkleclt
Автор

Omg. Thank you. I've been using vlookup my whole life and have to condition the table to be exact. This is really help me a lot

sillypotato
Автор

If you have formulas that are 4, 5 or more lines long and have dozens of levels of parentheses... then ALWAYS use the shortest and most unnested variant. That's why I don't have any favourites, I use lookup, vlookup, xlookup and match/index as well without any dogmas.

Tip: If your spreadsheets are too slow, it helps tremendously not to always process all 2^20 cells per column.

tabbycat
Автор

Great help. Been looking up excel features for much of this workday and this is one of the best things to simplify a ton of work.

Seeker
Автор

You are a born video instructor. Your voice is engaging, your speed with the mouse is not too fast and not too slow, and your progression made sure I did not get lost. I am absolutely coming back to watch this again when I need another refresher. Subscribed!

Автор

Thank you for all your videos. You are a great trainer.
Please how can I Compare two lists A and B in different workbooks? List A is the standard. If there are differences in List B, we copy the correct value from A to list B.

soneeric
Автор

Thank you for giving me such easy way to remove the need to organize the source data before moving to vlookup. I think I can now point the time before your tip and after it.

siryoneyal
welcome to shbcf.ru