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????
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! Ü
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!
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'm building out a website and to bulk import my items I use a csv file. I was dreading having to pull data from one of the excel documents as I was going to have to use a vlookup. I wasted a whole day trying to get the vlookup to work. Today I was going to try again. I’m glad that I was procrastinating and opened up my TH-cam page first. I'm not sure how your video got on my home page, but I'm glad it did.
I didn't try the vlookup today. Instead, I opened your video, and I’m glad I did you saved me another day of not getting anything done. Your steps are very clear as you explain them and what you should expect to see happen as you complete each step. Even with the data being in two different workbooks. I was able to get the data on the first try with your steps. Thank you for such a great video.
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.
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!
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!
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.
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.
@@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.
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.
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.
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.
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.
I did in fact stop using the vlookup long time back and switched to index - match. Just love it. So powerful.
ปีที่แล้ว +1
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 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.
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 :-)
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.
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.
Did I tell you how much time this saved me. I had to work with over a million records, 10 files of 100000 each, 4 times. Without this I would have died before completing my work. Thank you!
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.
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.
@@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.
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.
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.
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.
@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!
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. 🎉
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)
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!!
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.
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.
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 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! 😍🙏👍😁
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 helpful explanation of how to use INDEX MATCH. However, the first 5½ minutes explains VLOOKUP which I’m not interested in. New users could start 5½ minutes in and get what they need. Hope this helps :)
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.
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 !!
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.
I have been struggling with this formula for days. Thanks so much! I want to do a vlookup on another sheet first, if the data is not listed on the other sheet I want the data to be taken from another cell, however, if the cell selected is blank, then return with a text. This is what I have so far and it is not working. IF(ISNA(VLOOKUP(A1,'Cargoo New Shipments'!$B:$AD,29,FALSE)),E1,IF(E1="","Lookup ETA","Lookup ETA")) Lookup value from the other sheet first - VLOOKUP(A1,'Cargoo New Shipments'!$B:$AD,29,FALSE)) If the data is not listed, then take the data from cell E1 If cell E1 is "" (blank) then return with a text, "Lookup ETA" I hope this makes sense, thanks so much!!
@@ExcelLevelUp hi, I thought isna was a replacement for IFERROR. I want to lookup value on another sheet, if the value is not there, than take the value from a selected cell, if that selected cell is blank, than I want the formula to return with a text. I hope this makes sense. Thanks!!
Would IFNA be better than ISNA? IFNA will keep the vlookup results if it's found, then you can add logic on what to do if NA is returned. Thanks for re-introducing me to IF/ISNA. I had forgotten they existed. I might do a video on it, giving you credit.
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.
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..
both functions have their own pros and cons. it will depend on your discretion. i have been using index match tremendously but sometimes certain situations call for vlookup.
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
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.
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?
All this time I have been using Index, Match differently. I would type and mouse click, =INDEX(F2:H4,MATCH(B2,F2:F4,0),2). Strangely, this is the way I was taught when extracting from databases!
I work in asset management accounting and we are heavily rely on excel. Working papers range up to about 40 worksheets in one file, links and formulars are everywhere because I personally do not like to hard code numbers as there is no way to trace where that number came from. The problem I have encounter with vlookup is that the lookup_value has to be in the colume before the value you are looking for because the index_number has to be >0. Meaning if I wanted to lookup for something that is in column A but my look_value is in column C, even if I highlight all the columns including A it wouldn't work, because my index_number cannot be -2. I usually use these to look up for the loan interest value on specific dates, and I resolved to use SUMIF since almost 100% of the time we are looking for an amount and not other information, but I can see that this would be really useful if we are looking for information that is other than amounts. Thank you for sharing!
I use VLOOKUP on the fly every day for throw-away spreadsheets (csv output from multiple databases) to produce hasty analyses and reports. I haven't used INDEX MATCH, but it might come in useful. I know that VLOOKUP can work with dirty data, that is, it can read multiple data points and extrapolate an answer. I don't do that often, but I would have to look at how well INDEX MATCH does that.
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! Ü
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'm building out a website and to bulk import my items I use a csv file. I was dreading having to pull data from one of the excel documents as I was going to have to use a vlookup. I wasted a whole day trying to get the vlookup to work.
Today I was going to try again. I’m glad that I was procrastinating and opened up my TH-cam page first. I'm not sure how your video got on my home page, but I'm glad it did.
I didn't try the vlookup today. Instead, I opened your video, and I’m glad I did you saved me another day of not getting anything done. Your steps are very clear as you explain them and what you should expect to see happen as you complete each step. Even with the data being in two different workbooks. I was able to get the data on the first try with your steps. Thank you for such a great video.
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.
AGREED! Vlookup is easier to learn and understand. Seems like MATCH is many steps and therefore invites mistakes.
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!
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.
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.
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.
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.
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.
I'm a convert! Really useful to follow along and then follow-pause as I applied to my scenario to first try using this. Thank you.
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.
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
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!
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.
I did in fact stop using the vlookup long time back and switched to index - match. Just love it. So powerful.
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 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.
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.
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 like the INDEX and MATCH functions, thank you sir
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.
Very valuable contribution! Many thanks.
A quick aside: parentheSIS is the singular, parentheSES is the plural. You can't have a single parenthesee...
Did I tell you how much time this saved me. I had to work with over a million records, 10 files of 100000 each, 4 times. Without this I would have died before completing my work. Thank you!
Love to hear. Thanks for watching and commenting. Good luck with that much data.
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!
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.
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.
Ugh! How are you literally the most exceptional human in the world?!?! This made my work so much easier!
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.
@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!
you are my hero! vlookup never works like I want it to but this seems to do exactly what I want.
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!
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. 🙏🏾
Good explanation. It was really confusing in other channels. Wanted to master this . Thank you so much.
Happy to help. Good luck with Excel
I use index match sometimes, but have been using xlookup since 2 years ago when it first got released
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!!
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.
:) agree. If you don't have Excel MS 365 or Excel 2021, then INDEX&MATCH is great option
I have been struggling with VLookup between two 90K+ worksheets Index Match resolved the issue and runs in seconds Thank You
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.
Video might have been relevant 20 years ago. I always preferred index/match over vlookup.
Someone should tell this guy about xlookup.
No way!!! That’s crazy. I am a believer now. Thank you.
Use vlookup many times a day. This video has changed my life for the better! Wow! Thanks!!!!
Glad it helped!
Really helpful. VLOOKUP is indeed very useful but has its shortcomings as you mentioned. INDEX MATCH is easy to implement and understand.
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.
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.
Thank YOU! That worked well and the flat spot on my forehead is beginning to heal!!
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!
I was having a challenge with Lookup then I stumbled upon this rare gem.
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.
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.
Great help, couldn't figure it out with Vlookup, but this worked 100%
You can also use named tables and you can call specific columns by name
Very helpful explanation of how to use INDEX MATCH. However, the first 5½ minutes explains VLOOKUP which I’m not interested in. New users could start 5½ minutes in and get what they need. Hope this helps :)
Noted
Thank you for this, struggling with vlookup on a project and this literally just saved it.
Great to hear!
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.
VLOOKUP - goes garbage.
You did save my day, bloke!
Thank You very much. This one was very helpful!
Alternate for vlookup, but this makes also a key question for an interviewer, even we can achieve the task in many ways...
OMG! Where have you been all my life!!! Thank you for this! Very helpful!!
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 !!
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.
I have been struggling with this formula for days. Thanks so much!
I want to do a vlookup on another sheet first, if the data is not listed on the other sheet I want the data to be taken from another cell, however, if the cell selected is blank, then return with a text. This is what I have so far and it is not working.
IF(ISNA(VLOOKUP(A1,'Cargoo New Shipments'!$B:$AD,29,FALSE)),E1,IF(E1="","Lookup ETA","Lookup ETA"))
Lookup value from the other sheet first - VLOOKUP(A1,'Cargoo New Shipments'!$B:$AD,29,FALSE))
If the data is not listed, then take the data from cell E1
If cell E1 is "" (blank) then return with a text, "Lookup ETA"
I hope this makes sense, thanks so much!!
Are you looking to display the matched data from the New Shipments sheet? If so, wouldn't the ISNA prevent that?
@@ExcelLevelUp hi, I thought isna was a replacement for IFERROR. I want to lookup value on another sheet, if the value is not there, than take the value from a selected cell, if that selected cell is blank, than I want the formula to return with a text. I hope this makes sense. Thanks!!
Would IFNA be better than ISNA? IFNA will keep the vlookup results if it's found, then you can add logic on what to do if NA is returned. Thanks for re-introducing me to IF/ISNA. I had forgotten they existed. I might do a video on it, giving you credit.
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.
Awesome! No need to preconfigure data for VLOOKUP. Thanks
great walkthrough. I love the way the video was organized and how you stepped through each part!
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'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..
both functions have their own pros and cons. it will depend on your discretion. i have been using index match tremendously but sometimes certain situations call for vlookup.
Good point. XLOOKUP may be the best of both if you're on the latest versions of Excel.
@@ExcelLevelUp sadly xlookup's not available in google sheets :(
Thanks very much. I have been using vlookup all this time but appreciate this video. I will switch now. Thanks once again.
Thank you for sharing.. it helped me fix my issues in Excel
Thanks for sharing the tips. My question is what if a table/ range is not mentioned. Would it work on just few columns and rows?
Yes, absolutely
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.
Have been using Index Match since 2015. By far works better than the lookups.
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
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.
Excellent teacher. Thank you so much for your valuable lesson. Liked & definately subscribed. Looking forward to more
Thumbnail was so nice to see. I do have similar video in my page as well. But, demonstration is different. Yet, I don't know why i don't get comments.
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.
very good approach to simply utilize it instead of 'vlookup' function
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.
BRO, I wanna give you a big hug.
You make this so easy to undertand. Is there a way to update multiple columns at once?
And just like that, Vlookup is toast! Thanks so much for this! 🙏🏽
No doubt!
thanks a lot dear, i had a lot of mistakes in VLOOKUP BUT THESE seem to be too accurate
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?
Really simplified index matching for me, thank you!!
Thankyou for your input, i always vlookup at my work. But with this index match maybe can very easy. 💕
All this time I have been using Index, Match differently. I would type and mouse click, =INDEX(F2:H4,MATCH(B2,F2:F4,0),2). Strangely, this is the way I was taught when extracting from databases!
Simple and well explained. Able to solve like a magic.
If the problem is inflexibility, you can still use MATCH inside VLOOKUP. But main data still needs to be in first column.
Thank you! From a long-time vlookup user.
Thanks this was a huge help. wonder if i am supposed to acknowledge you in my thesis now 🤔
I work in asset management accounting and we are heavily rely on excel. Working papers range up to about 40 worksheets in one file, links and formulars are everywhere because I personally do not like to hard code numbers as there is no way to trace where that number came from.
The problem I have encounter with vlookup is that the lookup_value has to be in the colume before the value you are looking for because the index_number has to be >0. Meaning if I wanted to lookup for something that is in column A but my look_value is in column C, even if I highlight all the columns including A it wouldn't work, because my index_number cannot be -2. I usually use these to look up for the loan interest value on specific dates, and I resolved to use SUMIF since almost 100% of the time we are looking for an amount and not other information, but I can see that this would be really useful if we are looking for information that is other than amounts. Thank you for sharing!
Have you tried using XLookup? This lets you lookup values to the left or right of your index column. Big improvement in flexibility over VLookup.
Thank you so much for to this breakdown! I'll definitely be using these functions a lot more in the future : )
I use VLOOKUP on the fly every day for throw-away spreadsheets (csv output from multiple databases) to produce hasty analyses and reports. I haven't used INDEX MATCH, but it might come in useful. I know that VLOOKUP can work with dirty data, that is, it can read multiple data points and extrapolate an answer. I don't do that often, but I would have to look at how well INDEX MATCH does that.
Very nice this should allow workin multiple arrays with correlating results in a single cell no?
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.
Awesome explanation. Heads up👍