Stop using VLOOKUP in Excel. Switch to INDEX MATCH
ฝัง
- เผยแพร่เมื่อ 5 มิ.ย. 2024
- 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
Photo credit: www.pexels.com/@italo-melo-88...
Can you also make video on Xlookup!?
Here you go: XLOOKUP is the solution 💰💰💰
th-cam.com/video/8WOo28G7AOo/w-d-xo.html
@@ExcelLevelUp Thanks
Sheet 1, A column have a to z values, fetch/paste a to z each letter in each sheets at fixed cell. Example....
Sheet2 D4 cell need letter a,
Sheet3 D4 cell need letter b
Sheet4 D4 cell need letter c
Please teach me how to do????
@@prabalthakur1202😢😊
Ya I was thinking Xlookup seems to do this, but without the formula nesting and all the associated parenthesis. Perhaps there are cases where you'd need index match where Xlookup is inflexible though.
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! Ü
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.
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.
great walkthrough. I love the way the video was organized and how you stepped through each part!
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!
You're welcome!
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.
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!
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!
Glad it helped you. I appreciate you watching and commenting.
I did the same. Then I continued watching the video and switched to using INDEX MATCH haha.
It didn't 'help that it took 5 minutes before he describes INDEX MATCH, or that he didn't show that INDEX MATCH is faster than VLOOKUP, or that his example always had exact matches, or that INDEX MATCH handles the #NA problem, or how to make it work if say the thing you want to lookup is a superset or subset of the match, or that VLOOKUP is ONE formula, but INDEX MATCH is two formulas.
I did in fact stop using the vlookup long time back and switched to index - match. Just love it. So powerful.
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.
This is so important. There’s a ton of complicated formulas that you can include when creating the spreadsheet, but if your end users can’t figure out how to use it, then it’s not worth it to use.
Also, you can now use the xlookup function to do this same thing but much easier.
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.
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.
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
Also check out my XLOOKUP video. If you're on the newer versions of Excel you may find that XLOOKUP is easier.
Excellent teacher. Thank you so much for your valuable lesson. Liked & definately subscribed. Looking forward to more
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!
Glad it helped you.
Really helpful. VLOOKUP is indeed very useful but has its shortcomings as you mentioned. INDEX MATCH is easy to implement and understand.
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.
Happy to help!
Ugh! How are you literally the most exceptional human in the world?!?! This made my work so much easier!
OMG! Where have you been all my life!!! Thank you for this! Very helpful!!
Thank you so so much for this video. You have helped me solve some issues I was having with the standard Vlookup formula. Really appreciate you… Much love! 😍🙏👍😁
Great to hear!
Loved it, slow and easy to grasp lesson! Thanks much!
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.
you are my hero! vlookup never works like I want it to but this seems to do exactly what I want.
Thank you for this I am trying to make an inventory excel book for work. This helped so much. I looked all over google and finally found. This was the answer that I needed.
Great. Feedback like this is why I create videos. Thanks for watching.
This needs to be spread as a message of hope! Too long we have been suffering vlookup tyranny!
We need more true believers like you.
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.
I couldn't understand on your tip. Are you saying that slowing down spreadsheet is good. Or you prefer index match for less processing.
@@fahimaftab9486 Neither. The tip refers to the theory that VLOOKUP slows down the sheet. I suggested that first of all you should refrain from using whole columns instead of a limited range, because then the array becomes directly 1048576 rows long and THAT really slows down.
Regarding not to select whole columns to increase speed: that is not correct. Excel does not store empty cells because it uses "sparse arrays".
Example: when you only fill cell A1 and A999 and leave cells A2:A998 blank Excel only stores (and processes) 2 cells.
Speed is mainly determined by the number of non-blank cells in a range and the amount of memory Excel can use.
If Excel had tot process empty cells and if we assume a 10^2 x 10^2 cell matrix Excel would have process 1.099.511.627.776 cells for every change.
In that case "slow" would be the euphemism of the century ;-)
HTH
BTW: using the best tool in the box is very sound advice. Keep it as simple as possible but not simpler...
@@montebont Excel does not store empty cells, that ist correct. But when the formula contain a complete column (like A:A) and when array processing ist active (always for O365) then this formula will process all the cells.
I can't tell you how long I searched for a solution like this! Thank you SO much for this. Way less messy than a vlookup.
Good explanation. It was really confusing in other channels. Wanted to master this . Thank you so much.
Happy to help. Good luck with Excel
Thank you for showing the F9 formula troubleshooter. That helped me solve an issue with a complex Xlookup function that had embedded Index and embedded Match within that. Turns out I just needed to change the type of Match! Could not have figured out the problem without F9.
Glad it helped!
Thank you so much much for this video. This example is extremely similar to what I need to do for work all the time in education. I was able to learn both v look up and index match here in about 10 minutes. 🎉
You're very welcome!
Thank you for sharing.. it helped me fix my issues in Excel
Guys upgrade to Office 365. The functions released for this version of Excel will make your life easier. I started using Office 365 just an year ago, and I learned a set of new functions (XLOOKUP, FILTER, SEQUENCE, SORT, SORTBY, XMATCH, UNIQUE, LET, LAMBDA) that can be used in combination with the old functions and make you 100 times more efficient. Also if you want to be efficient learn Power Query which is an Excel add-in.
Really simplified index matching for me, thank you!!
Thanks, I've used Index/Match in the past, but never really understood what it is doing. Great explanation. I think that XLOOKUP, though, is a great replacement for Index/Match and V/HLOOKUP.
No way!!! That’s crazy. I am a believer now. Thank you.
Index match is a wonderful function, which has been in excel since the 1990s. The critical thing about using this is the datatable you are reading from, which needs to be a clean table, pivot table etc with all the data you need in it. If you need to do a lot of lookups using index match for differing bits of data, it will slow your spreadsheet down so the more relevant data you can get into your original data, the better. One of the things I do to match up data from one table to another is to create a unique match in both tables from 2 parts of data using concatenate command (or "&"), as this removes duplicate references. One tip if you know you do not have data in the table you are looking up is to encapsulate the index match function within "iferror(.....,"")" which will return a blank cell (or zero if you use "0") instead of n/a.
100% backed up!
But I am not sure we are trained enough to match someone who can, somehow, reduced the latency from a VLookup to a composite formula like an Index Match or Index Match Match, which he states is even easier to comprehend, when it was harder to me! I think I might have skipped a few classes!!
Thank you so much for to this breakdown! I'll definitely be using these functions a lot more in the future : )
Very valuable contribution! Many thanks.
A quick aside: parentheSIS is the singular, parentheSES is the plural. You can't have a single parenthesee...
Thanks very much. I have been using vlookup all this time but appreciate this video. I will switch now. Thanks once again.
Vlookup is commonly use due to the easy and fast you can create it. But indeed, index and match are good combination to pull data like coordinates (x, y)
XLOOKUP is faster. It also doesn't care about position.
Very useful, it also eliminates me having to count columns and wasting time when I am using vlookup. Great video!
@@rob-fb5xs care to elaborate? thanks
@@rob-fb5xs yes that makes sense, the answer usually simple. I appreciate the response. Thanks again. 🙏🏾
I'd rather use xlookup than using the complex index match formula.
XLOOKUP is definitely a good alternative.
Wanted to try, but my version not supported... So sad
@@sharulnizam9488
It yet yyys ttttttytt
WTF
I was just thinking the same
Thankyou for your input, i always vlookup at my work. But with this index match maybe can very easy. 💕
Thank you for this, struggling with vlookup on a project and this literally just saved it.
Great to hear!
I'm surprised that no one mentions FILTER function in the whole VLookup vs Index + Match debate. It just feels so much more versatile and intuitive than the alternatives. If you want to use it as lookup, just filter down the column/table to a single cell that meets all criteria. Even if there are multiple matches, I feel like it's often better to use some aggregation function or let it spill rather than accepting the first match found.
i think filter is not available in older excel version tho. but it's quite powerful than lookup actually
If you're saying FILTER function is better than VLOOKUP and INDEX formula, I think you're preparing menu chart for restaurant.
CTRL-F and filters are limited to data in just one sheet; they fetch results only from the sheet to which they're applied. VLOOKUP/Index Match
on the other hand is used for operations across multiple sheets.
Thank YOU! That worked well and the flat spot on my forehead is beginning to heal!!
I started by indexing entire columns - I soon found this slows everything down so I amended my operating procedures to use tables - this allows only indexing of populated areas and also allows lookup ranges to be dynamic too . I am verymuch a horses for courses person and will happily use, in order of preference, XLookup - Index Match - VLookup when required.
Very easy to understand. Thank you
Amazing and clear instruction! I am very appreciated. Thanks
You can use as a second argument inside vlookup a set of arrays just using the {}, ergo, it doesn't matter the first column, ergo, one function for all.
thank you heap,s Ive recently taken on the task of learning python to do all the crunching simply becasue the spreadsheet was too slow due to a few thousands vlookup statements on 300 or so pages! I'll check this out now. cheers!
I have been struggling with VLookup between two 90K+ worksheets Index Match resolved the issue and runs in seconds Thank You
Thanks. Great presentation. I still get some N/A messages and the odd incorrect entry. But that's nothing to do with your brilliant presentation, I suspect it's some formatting problems. But I really like the approach you suggest. Thanks for posting. Really helpful.
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.
As some have stated, office 365 has Xlookup, which works great. However if you are sharing excel sheets with others who do not have office 365, the formula wont work. Just something to keep in mind :-)
Good point. Thanks for watching.
OUTSTANDING point, I share spreadsheets a fair amount & many students have Libreoffice or another shareware app that generally lags Excel in implementing new functions. So Xlookup is a few years away and shared spreadsheets using it will be broken for the recipient's. Xlookup mo bettah, just not universal.
I use Index/Match for PRECISELY this reason. Yes Xlookup, Filter etc are "better" methods but I/M has greater compatibility on external documents.
I use index match sometimes, but have been using xlookup since 2 years ago when it first got released
brilliant, well explained and super useful for the data I am working with
Happy to hear that it was of use for you. Thanks for watching and commenting.
@@ExcelLevelUp xlookup is not available to all
I like the INDEX and MATCH functions, thank you sir
You illustrated so well. I'd appreciate if I you could explain "MATCHING partial text of the cell", not the entire cell value, to an Array? Do you have it explained elsewhere?
Awesome! No need to preconfigure data for VLOOKUP. Thanks
clear explanation!
how about counting up everything in a row that meets a criteria, for a person in a list. so in English:..'look up "Mary" in a list and count every cell in the row adjacent to her name that is less than 7". 7 is hours and each column is a workday, so its counting how many days Mary was late.
and the same thing for adding g up the hours she worked.
I love indexing matching! My go to for nearly all my task
If you're on a newer version of Excel, please check out XLOOKUP as well.
Video might have been relevant 20 years ago. I always preferred index/match over vlookup.
Someone should tell this guy about xlookup.
@9:06
Mines a bit more complex Index Match formula. Since I have a long spreadsheet, if nothing is inputted in Column B then it will display a blank instead of #N/A.
=ARRAY_CONSTRAIN(ARRAYFORMULA(iferror(index(I:I,Match(B2,H:H,0)),"")), 1, 1)
Keep up the great videos!
I'm used to VLOOKUP, which helps of course, but just looking at the number of steps involved it seems a lot simpler.
To avoid the results getting messed up if I add a column, etc., and also to simplify the spreadsheet, I nearly always just copy the column with the VLOOKUP and then paste it in place as just Values. That way the formula is replaced by the information it found.
Of course, that doesn't work if the data the lookup targets is subject to change..
You can also use named tables and you can call specific columns by name
LOVE IT! THANKS FOR YOUR GUIDANCE
Vlookup is brilliantly simple. Use named ranges or tables for easy formula and adding of columns. Index and match is great also as you don't have to search from left to right but beware, as index needs the row and column number which is why you match to identify the Row and then if need match to find the column number and can give the result as the cell before the result desired. But this is long winded. You can use match with nested multiple criteria but you would need to ctrl+shift+enter as array formula and will wipe out performance with big data sets. Use vlookup properly with relative references or named ranges or tables for simplicity. You can always combine vlookup and hlookup. It is not better to use one over the other, only in the context for use.
It's a horrible idea to combine vlookup and hlookup, it's the old way of doing things.
Systematic explanation and easy. Thanks for efforts.
Very insightful guide. thanks alot!
Thank you for that - very clearly explained! One thing that's been driving me insane is wanting to convert the values in my VLOOKUP tables back in reverse (e.g. look up a customer name to get their ID in your example). This allows me to use one table of data and I can look up the matching pair of either.
There's no reason you can't do that. I would caution or at least understand the risks in matching names. If the customer list is for many customers, you may run into duplicate customer names. This could lead to incorrect matches.
Thanks, I found this really helpful!
wow this is such a simple yet very smart strategy. Thanks!
Great. This is a better system for me as i have sheets with over 1k lines, so index match tells me more and is more flexible than vlookup or countifs. Thanks!
Nice explanation. Is there a way of doing vlookup or index match over 2 columns ? that look up over col1 and col2
Thankyou admin
this formula useful for me and I will use it in my future projects
:) agree. If you don't have Excel MS 365 or Excel 2021, then INDEX&MATCH is great option
Saving this video for work on Monday.
superb, life changing, thanks so much!
Great video! I ran into a problem when I was trying to reference a cell for the Match Lookup_Value that contained a formula. Couldn't seem to find a way around that problem.
Agreed this addresses a couple issues with vlookup - the column reference and the ordering of data issue. However, I don’t think you can say this is simpler! Also in terms of spreadsheets with the amount of data you reference, I would actually think excel overall shouldn’t be the tool you use - instead using sql would seem to be better
I agree that lookup functions can eventually exceed Excel. I've run some multiple criteria XLOOKUPs recently that have shut down my laptop for several minutes.
Brilliant. I would like to know if it is possible for you to do a video about how to link a cashbook and with costs by categories in different worksheets. Much appreciated
wish i had come across this earlier - thanks , simple and well explained
You are welcome!
this is exactly what I needed, thanks
Hi, First, thanks for the accurate explanation, secondly, if we have two different names with the same value, how do we arrange them in the ranking table?
Let us know if you can, thank you very much. hope to see my comment !!
That was so helpful... Many Thanks
Thank you for the video, very well explained
Thanks bro.. it’s super cool let me put in my daily tasks
thanks a lot for the explication for example how i can use to do a report of all equipments with status "in-service"
Awesome explanation. Heads up👍
Thank you very much. I have given it a try in a file with a lot of rows (hundreds of thousands) and INDEX MATCH seems way quicker.
Happy that it helped you.
So as for your arguements for the format that it has to be in for it to work,
I have used VLOOKUP([@[Column4]],CHOOSE({1,2,3,4},Table[Column1],Table[Column6]),2,FALSE) in order to go in reverse.
I have also used Vlookup(A2,C2:D10,counta(C1:D1),false) in order to allow me to add more columns without having to change my vlookups.
But, that being said, this is pretty great, because of the faster loading as I have over 1000 tables in my program with thousands of Vlookups and many filter() etc. So thank you for this, it has been very helpful. I subscribed because of this video. Looking forward to checking out more of your stuff.
EDIT: Just an update, I just replaced 118,575 vlookups in my larger spreadsheet and it is loading much faster.
Great advice about handling the column adds.
Great video demonstrating a more flexible alternative to VLOOKUP() formulas.
My personal preference is to use Power Query, which eliminates the use of formulas entirely.
Just seen the video and I liked very much!
I use 2 lookup functions: LOOKUP and XLOOKUP. Haven't used I/M for at least 2 years. Sometimes I use FILTER(FILTER) for two-way. When I do need a match function I use XMATCH.
Thanks for great advice that others should follow. I plan to look up XMATCH today.
@@ExcelLevelUp LOOKUP is an often overlooked function. It is the oldest spreadsheet function but not obsolete. VLOOKUP has one clear advantage over LOOKUP: General case exact lookup. But LOOKUP is a bit easier to use than VL and can do "lookup left" which is a well known issue with VL. It serves most needs for approximate match or sorted exact match. Quick and easy set up. When VL was crested the only major gain was general exact match option but it sacrificed lookup to the left and has more required arguments. XL is by far the best option for exact match and for situations where its more robust arguments are needed. Also XL can be more easily used with match functions and can be wrapped in itself or combined with FILTER for more complex situations.
I was having a challenge with Lookup then I stumbled upon this rare gem.
Great lesson
Thank you
Thank you for kind & specific informatoin
this was very useful. thank you