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'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.
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.
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.
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!
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!
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.
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.
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.
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.
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.
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.
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!
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.
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.
ปีที่แล้ว +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 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.
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.
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.
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.
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.
@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!
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 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. 🎉
Hi! I’m still using an older ver of Excel, but still it suits my needs. Question is, using the Index-Match functions, how can I display the results horizontally. Thanks!
How do I add pure time together? For example I ran for 1:03:26:06 today and 1:15:37:13 how do I get the sheet to add the time together and know that the seconds, min and hours are added correctly?
Could you make a video of how to merge two sheets that contains eg. product numbers in both sheets. But in the first sheet you may have numbers that don't apear in the second, and vica verca, and some are common. My problem is that I want them to apear in a third sheet with all the numbers, but only apear once. And its only one collomn, so it shoulden't be complex? But I don't know how. Help please 🙏
Is there a way to do a two-way vlookup? Like at 3:30 what if the blue table had multiple rows with duplicate customers for each purchase they made, and I want to pull the customer's name based on matching customer ID to purchase date? So suppose at 3:30, the data on screen, both tables share dates and customer ID. Blue table has George Washington showing up three times on three different days to make three different purchases. I want to get the customer name IF dates and ID match. Obviously in this example that would not be necessary, but I have a use case scenario where it is and figuring out that syntax would be 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! 😍🙏👍😁
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.
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.
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.
Good video. In the lead in it was mentioned that VLookup slows a spreadsheet down but I didn’t see that Index Match would prevent that. Can someone help me understand if it will improve spreadsheet performance?
Normal VLOOKUP usage requires to you to pull in all of the necessary columns which could consist of as few as 2 or 20. I find Index Match or Xlookup to be more efficient as you only how to define the 2 columns that you need. Efficiency depends on your data size.
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 dont know why it didnt do all of it..." found at 4:44 made me giggle and is my biggest complaint with Excel. My question, and I will play around with this later today, what happens if you needed to add rows? For example, looking up Name to get Customer ID. Names can be added to a table, the table then resorted. Will the formula hold?
like everything, it depends. If you define the lookup table as A1:C5 and start typing in row 6, you're in trouble. But if you reference the lookup table columns as A:C, it won't care if you add rows as it reads the entire column
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
Is there a way to look for multiple values? i.e. if ColumnA has "apple" and ColumnB has "red" then display ColumnC's "red apple" but if ColumnB has "blue" then display ColumnC's "blue apple" ?
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)
Hi. Thanks for sharing the differences between VLookup and IndexMatch. I noticed that you didn't $lock$ the lookup area (column H and I), by typing F4 after the selection of the two columns. That's why the Vlookup failes after adding an extra column in the vookup area. Otherwise it would work perfectly fine, right?
i want to know if we can use INDEX MATCH for our inventory daily sheet. We are receiving it on thousands of items on daily basis, but always changed the Qty. So is there a easy way in INDEX MATCH , how to sort out the changes in different Colum and identify the changes.. if some fields are not change, its show 0
Do you have any advice on a function that cna be used to select the ranges in which the lookups will be done, automatically based on criteria? Like address, but a range rather than a single cell?
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.
What if I have some duplicated "customer ids" so to speak would the match-index function work properly? I usually have sub-category in the same column, which causes me so much trouble in vlookup. I'd have to manually select arrays in a same column which is a pain in the ass
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.
Hi, Thanks for the great video. I cant figure out my problem here: My match data is in a merged cell. (Lets say id 1001 is in H2+H3). How to index them in this case? Can you please help?
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!!
Hi I always found your videos the best and it really does levelup. I am now working on a project where i need to sync data between two different spread sheets. Please let me know how to sync a specific column values with another sheet csv sheet with same column but different values ? I am basically syncing two column values with other. Hope i have been able to explain as its a bit confusing. Thanks
Can you make a simple entry form that auto pulls data from spreadsheet like employee id and rest comes up, then hit a button to create an entry for that person in second database called live tickets, an edit feature to make changes to that live tickets, then export certain live tickets entries that are complete into another spreadsheet called completed tickets?
Can I ask if you say have 2 excel spreadsheets which you want to do a quick comparison with . but the row has 6 fields you want to compare for changes you want to check instead. The sheet is not organised beyond, cable description, terminal end 1 type, terminal end 1 name, Terminal end 2type, terminal end 2 name, length ? the only thing that should be the same each time is the cable description but not always. how do I check these without spending ages look from one to the other.
You can pull down a formula from the lower right corner of a cell. I only utilize this when I need to drag it down a few cells. You can also double click on the lower right corner to have a formula fill down. I use that method on larger spreadsheets.
I followed every single step as shown in the video. I even created the exact same cells as yours just to try it out. My problem is that when I type a comma the formula does not work for me as shown in the video. To be more specific at 3:36 you type b2, then you type a comma and it allows you to select column H including it in the formula. When I select column H I get an error. Can anyone help please?
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'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.
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.
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.
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.
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!
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.
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.
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.
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.
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.
:) agree. If you don't have Excel MS 365 or Excel 2021, then INDEX&MATCH is great option
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.
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!
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.
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.
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 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 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!
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 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 use index match sometimes, but have been using xlookup since 2 years ago when it first got released
OMG! Where have you been all my life!!! Thank you for this! Very helpful!!
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.
Ugh! How are you literally the most exceptional human in the world?!?! This made my work so much easier!
6:00 What if you move the table so customer ID is no longer on column H? Maybe instead do Table2[Customer ID] so it auto adjust if you move the table?
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.
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.
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
Thanks!
Good explanation. It was really confusing in other channels. Wanted to master this . Thank you so much.
Happy to help. Good luck with Excel
@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!
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.
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
Very valuable contribution! Many thanks.
A quick aside: parentheSIS is the singular, parentheSES is the plural. You can't have a single parenthesee...
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!
Hi! I’m still using an older ver of Excel, but still it suits my needs. Question is, using the Index-Match functions, how can I display the results horizontally. Thanks!
How do I add pure time together? For example
I ran for 1:03:26:06 today and 1:15:37:13 how do I get the sheet to add the time together and know that the seconds, min and hours are added correctly?
you are my hero! vlookup never works like I want it to but this seems to do exactly what I want.
Can you show example for index match on two criteria formula? This is great, thank you.
I like the INDEX and MATCH functions, thank you sir
Could you make a video of how to merge two sheets that contains eg. product numbers in both sheets. But in the first sheet you may have numbers that don't apear in the second, and vica verca, and some are common. My problem is that I want them to apear in a third sheet with all the numbers, but only apear once. And its only one collomn, so it shoulden't be complex? But I don't know how. Help please 🙏
Is there a way to do a two-way vlookup? Like at 3:30 what if the blue table had multiple rows with duplicate customers for each purchase they made, and I want to pull the customer's name based on matching customer ID to purchase date?
So suppose at 3:30, the data on screen, both tables share dates and customer ID. Blue table has George Washington showing up three times on three different days to make three different purchases. I want to get the customer name IF dates and ID match.
Obviously in this example that would not be necessary, but I have a use case scenario where it is and figuring out that syntax would be very helpful.
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. 🙏🏾
No way!!! That’s crazy. I am a believer now. Thank you.
Great help, couldn't figure it out with Vlookup, but this worked 100%
Thank you for sharing.. it helped me fix my issues in Excel
I have been struggling with VLookup between two 90K+ worksheets Index Match resolved the issue and runs in seconds Thank You
I did in fact stop using the vlookup long time back and switched to index - match. Just love it. So powerful.
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!
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.
You can also use named tables and you can call specific columns by name
Very nice this should allow workin multiple arrays with correlating results in a single cell no?
Really helpful. VLOOKUP is indeed very useful but has its shortcomings as you mentioned. INDEX MATCH is easy to implement and understand.
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.
Does cell format needs to same and can it be used between sheets
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.
Thank you so much for to this breakdown! I'll definitely be using these functions a lot more in the future : )
Good video. In the lead in it was mentioned that VLookup slows a spreadsheet down but I didn’t see that Index Match would prevent that. Can someone help me understand if it will improve spreadsheet performance?
Normal VLOOKUP usage requires to you to pull in all of the necessary columns which could consist of as few as 2 or 20. I find Index Match or Xlookup to be more efficient as you only how to define the 2 columns that you need. Efficiency depends on your data size.
Can you use Index Match for referencing the Grand Total in a PIvot Table?
You make this so easy to undertand. Is there a way to update multiple columns at once?
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 dont know why it didnt do all of it..." found at 4:44 made me giggle and is my biggest complaint with Excel. My question, and I will play around with this later today, what happens if you needed to add rows? For example, looking up Name to get Customer ID. Names can be added to a table, the table then resorted. Will the formula hold?
like everything, it depends. If you define the lookup table as A1:C5 and start typing in row 6, you're in trouble. But if you reference the lookup table columns as A:C, it won't care if you add rows as it reads the entire column
thanks a lot for the explication for example how i can use to do a report of all equipments with status "in-service"
Use vlookup many times a day. This video has changed my life for the better! Wow! Thanks!!!!
Glad it helped!
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
Is there a way to look for multiple values? i.e. if ColumnA has "apple" and ColumnB has "red" then display ColumnC's "red apple" but if ColumnB has "blue" then display ColumnC's "blue apple" ?
I know of a method to do this with XLOOKUP. I will look to create a video at some point but now I recommend that you google XLOOKUP multiple criteria.
Thank you for this, struggling with vlookup on a project and this literally just saved it.
Great to hear!
Hi, I was wondering if you could help, the vlookup you showed me was fantastic, is there a way it can copy the colors of the cells too?
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.
Thanks very much. I have been using vlookup all this time but appreciate this video. I will switch now. Thanks once again.
Hi. Thanks for sharing the differences between VLookup and IndexMatch. I noticed that you didn't $lock$ the lookup area (column H and I), by typing F4 after the selection of the two columns. That's why the Vlookup failes after adding an extra column in the vookup area. Otherwise it would work perfectly fine, right?
Excellent teacher. Thank you so much for your valuable lesson. Liked & definately subscribed. Looking forward to more
i want to know if we can use INDEX MATCH for our inventory daily sheet. We are receiving it on thousands of items on daily basis, but always changed the Qty. So is there a easy way in INDEX MATCH , how to sort out the changes in different Colum and identify the changes.. if some fields are not change, its show 0
VLOOKUP - goes garbage.
You did save my day, bloke!
Thank You very much. This one was very helpful!
Do you have any advice on a function that cna be used to select the ranges in which the lookups will be done, automatically based on criteria? Like address, but a range rather than a single cell?
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.
Thoughts on Xlookup vs Index and Match?
Thank YOU! That worked well and the flat spot on my forehead is beginning to heal!!
Top, but I didn´t catch the last part, were do I click for "exact" value in index match? Thanks
MATCH has a parameter for EXACT. MATCH(lookup_value, lookup_array, [match_type]) Set the match_type to 0.
great walkthrough. I love the way the video was organized and how you stepped through each part!
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.
What if I have some duplicated "customer ids" so to speak would the match-index function work properly? I usually have sub-category in the same column, which causes me so much trouble in vlookup. I'd have to manually select arrays in a same column which is a pain in the ass
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.
Simple and well explained. Able to solve like a magic.
wow this is such a simple yet very smart strategy. Thanks!
hi, can you show pivot tables done by macros?
Hi, Thanks for the great video. I cant figure out my problem here:
My match data is in a merged cell. (Lets say id 1001 is in H2+H3). How to index them in this case? Can you please help?
Thankyou for your input, i always vlookup at my work. But with this index match maybe can very easy. 💕
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!!
Hi
I always found your videos the best and it really does levelup. I am now working on a project where i need to sync data between two different spread sheets. Please let me know how to sync a specific column values with another sheet csv sheet with same column but different values ? I am basically syncing two column values with other. Hope i have been able to explain as its a bit confusing. Thanks
How does index match work if you need to match to data in 2 columns? Just a simple "&" concatenate like in VLOOKUP?
Can you make a simple entry form that auto pulls data from spreadsheet like employee id and rest comes up, then hit a button to create an entry for that person in second database called live tickets, an edit feature to make changes to that live tickets, then export certain live tickets entries that are complete into another spreadsheet called completed tickets?
I also like that the first column of the referenced dataset does not have to be in alphanumeric order as it does in VLOOKUP.
Can I ask if you say have 2 excel spreadsheets which you want to do a quick comparison with . but the row has 6 fields you want to compare for changes you want to check instead. The sheet is not organised beyond, cable description, terminal end 1 type, terminal end 1 name, Terminal end 2type, terminal end 2 name, length ? the only thing that should be the same each time is the cable description but not always. how do I check these without spending ages look from one to the other.
What exactly shortcut you do it @4:49? It's like replacement of dragging down with mouse? Thanks
You can pull down a formula from the lower right corner of a cell. I only utilize this when I need to drag it down a few cells. You can also double click on the lower right corner to have a formula fill down. I use that method on larger spreadsheets.
@@ExcelLevelUp Thanks I thought you were using a shortcut and I discovered it, its CTRL + D
I followed every single step as shown in the video. I even created the exact same cells as yours just to try it out. My problem is that when I type a comma the formula does not work for me as shown in the video. To be more specific at 3:36 you type b2, then you type a comma and it allows you to select column H including it in the formula. When I select column H I get an error. Can anyone help please?
I hope I am not late helping you,
Old Excel use ;
Latest Excel use ,
; Or , have the same function