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 RUclips 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!
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.
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!
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.
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.
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.
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.
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.
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.
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.
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.
Год назад+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 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. 🎉
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.
@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!
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)
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.
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! 😍🙏👍😁
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!
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'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.
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.
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.
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!
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.
If someone is confused On how VLOOKUO works then MATCH INDEX will surely go above their head. And btw, if you have MS 365 then XLOOKUP is the formula you are looking for.
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 :)
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!!
index match works well than vloookup. i'm battling for a week using vlookup although it finds some data in a rows but some rows it can't even find so i have to repeat the formula again and again. thanks bro!!!
Can you also make video on Xlookup!?
Here you go: XLOOKUP is the solution 💰💰💰
ruclips.net/video/8WOo28G7AOo/видео.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 RUclips 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!
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.
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.
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.
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
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.
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.
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 use index match sometimes, but have been using xlookup since 2 years ago when it first got released
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.
:) agree. If you don't have Excel MS 365 or Excel 2021, then INDEX&MATCH is great option
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!
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 have been struggling with VLookup between two 90K+ worksheets Index Match resolved the issue and runs in seconds Thank 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.
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.
you are my hero! vlookup never works like I want it to but this seems to do exactly what I want.
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!
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.
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!
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.
Thanks very much. I have been using vlookup all this time but appreciate this video. I will switch now. Thanks once again.
I did in fact stop using the vlookup long time back and switched to index - match. Just love it. So powerful.
Very valuable contribution! Many thanks.
A quick aside: parentheSIS is the singular, parentheSES is the plural. You can't have a single parenthesee...
Alternate for vlookup, but this makes also a key question for an interviewer, even we can achieve the task in many ways...
I like the INDEX and MATCH functions, thank you sir
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!
OMG! Where have you been all my life!!! Thank you for this! Very helpful!!
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.
Great help, couldn't figure it out with Vlookup, but this worked 100%
Use vlookup many times a day. This video has changed my life for the better! Wow! Thanks!!!!
Glad it helped!
No way!!! That’s crazy. I am a believer now. Thank you.
@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!
Ugh! How are you literally the most exceptional human in the world?!?! This made my work so much easier!
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.
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.
Really helpful. VLOOKUP is indeed very useful but has its shortcomings as you mentioned. INDEX MATCH is easy to implement and understand.
I was having a challenge with Lookup then I stumbled upon this rare gem.
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.
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. 🙏🏾
Thank you for sharing.. it helped me fix my issues in Excel
this is so much better and well explained. thank you so much
Thank you for this, struggling with vlookup on a project and this literally just saved it.
Great to hear!
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!
Simple and well explained. Able to solve like a magic.
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!
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!
VLOOKUP - goes garbage.
You did save my day, bloke!
Thank You very much. This one was very helpful!
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.
You can also use named tables and you can call specific columns by name
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.
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.
wow this is such a simple yet very smart strategy. Thanks!
great walkthrough. I love the way the video was organized and how you stepped through each part!
Good explanation. It was really confusing in other channels. Wanted to master this . Thank you so much.
Happy to help. Good luck with Excel
Thank you so much for to this breakdown! I'll definitely be using these functions a lot more in the future : )
Thank you! From a long-time vlookup user.
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.
And just like that, Vlookup is toast! Thanks so much for this! 🙏🏽
No doubt!
Video might have been relevant 20 years ago. I always preferred index/match over vlookup.
Someone should tell this guy about xlookup.
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
This also works in Google Sheets. Many thanks!
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.
Thankyou for your input, i always vlookup at my work. But with this index match maybe can very easy. 💕
This is a game changer. Thank you so much!
You're welcome!
Excellent Expert Exhibition for Excel.
Have been using Index Match since 2015. By far works better than the lookups.
Thankyou admin
this formula useful for me and I will use it in my future projects
very good approach to simply utilize it instead of 'vlookup' function
I used Vlookup for all of one week when I first start working years ago. I like index match because in allows for more than one match criteria.
Thanks for sharing!
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.
Saving this video for work on Monday.
Really simplified index matching for me, thank you!!
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.
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.
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.
Awesome! No need to preconfigure data for VLOOKUP. Thanks
If someone is confused On how VLOOKUO works then MATCH INDEX will surely go above their head.
And btw, if you have MS 365 then XLOOKUP is the formula you are looking for.
Great point. I have a recent video about XLOOKUP as well, and have started to use XLOOKUP in my job.
super useful for the data I am working with my report
Huge!, never using Vlookup again, Great video!
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
BRO, I wanna give you a big hug.
Awesome! I absolutely loathe VLOOKUP always seemed counterintuitive - thanks for alternative!
Very insightful guide. thanks alot!
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!!
index match works well than vloookup. i'm battling for a week using vlookup although it finds some data in a rows but some rows it can't even find so i have to repeat the formula again and again. thanks bro!!!
Thanks for sharing!
Awesome explanation. Heads up👍
Just seen the video and I liked very much!
Very helpful and timely (for me). Appreciate it .
Glad it was helpful!
Excellent teacher. Thank you so much for your valuable lesson. Liked & definately subscribed. Looking forward to more
thanks a lot dear, i had a lot of mistakes in VLOOKUP BUT THESE seem to be too accurate
THANKS BROTHERR
I SO HAPPY TO LEARN HERE