6 Incredible Excel INDEX/MATCH Lookup Examples - Workbook Included
Рет қаралды 428,624
6 жыл бұрынExcel Courses: www.teachexcel.com/premium-co...
Excel File: www.teachexcel.com/excel-tuto...
Excel Forum: www.teachexcel.com/talk/micro...
Examples start at the 6:40 minute mark.
6 Detailed examples of how to make and use Index/Match Lookup formulas and functions in Excel. This includes the basics of each function and how to combine them in advanced ways in order to make robust and useful formulas that are more versatile and helpful and better than any Vlookup or Hlookup.
You will learn about basic lookup formulas; lookup formulas with other functions nested inside of them; how to make horizontal lookups; how to make vertical lookups; how to make two-way lookups; and even how to combine a lookup with text manipulation functions in order to return only part of a result.
Learn more at TeachExcel.com.
@mkmstillstackin 5 жыл бұрын
This was really good, Thanks! I'd used Index/Max years ago but never this level. Thanks for the refresher and supercharger!
@RollinShultz 4 жыл бұрын
That is actually the best explanation of not only how, but why to use both types of functions to make a versatile search.
@TeachExcel 4 жыл бұрын
Thanks so much for your kind words)))
@FelPacs 2 жыл бұрын
I learned from this tutorial and i watched 3 times just to learn from this video because my work now requires more excell functions just to make my worksheet easy and faster..really thanks a lot from this tutorial..I will watch some of your video to learn more functions in excell. Have a great day ahead
@big1975E 5 жыл бұрын
Great video! I liked the formula to find the number after the dash in the part number. A shorter alternative formula would be to use the mid function. Mid( index(match()),search(“-“,index(match()))+1,99). Even though you just want one character putting 99 as the number of characters argument value means mid will find as many of the remaining characters as possible or 99 characters which ever comes first. This formula will work if the part numbers ever become more than one digit after the dash.
@engelberthk 5 жыл бұрын
Excellent, thanks for your examples
@gjmarsh8787 5 жыл бұрын
Professional & Informative!
@leoncarr920 4 жыл бұрын
Good stuff Bud...really good teaching style... ease to follow... Cheers
@johnwatkins39 6 жыл бұрын
As always, great videos! As I was watching it, i was thinking of other ways to use index and match functions.
@TeachExcel 6 жыл бұрын
I'm happy to have your thoughts and feedback on this! Maybe I can do a follow-up video to this one.
@NotSherine 6 жыл бұрын
Great , yes very helpful. Learnt something new with the last “monster “. Formulas, but great refresher
@TeachExcel 6 жыл бұрын
Glad I could teach you something new :)
@leoburnett9049 3 жыл бұрын
This tutorial is great. Thank you!
@azzamabdullah3469 5 жыл бұрын
you are a good teacher (Y)
@mahmoudhassan6627 3 жыл бұрын
Sir , You are already a Legend .. Thank you s much for this accurate and exact explanation
@kdumm7563 6 жыл бұрын
Excellent job. I NOW get how that works.
@TeachExcel 6 жыл бұрын
Yea I'm glad it helped :)
@keifer7813 2 жыл бұрын
Best explanation I've seen on this topic. Thank you so much
@mistycarter9396 5 жыл бұрын
Very informative. Thanks
@lemon-vk7wq 5 жыл бұрын
It is easy to understand. Thanks@):-
@xxczerxx 2 жыл бұрын
You can do a completely random index match if you pop the formula into VBA. It's very useful if the data required moves every day (if from the web and transformed into Excel via Power Query) so you can't say to look in row x or column y.
@banwarifageriaexcelclasses1447 5 жыл бұрын
nicely explained ..thank you sir ,
@johnkaggwa7903 4 жыл бұрын
Thank you for the great tutorials
@GeethMilan 4 жыл бұрын
Such a great lesson !!!
@tommyharris5817 3 жыл бұрын
Great tutorial. Of course, Conditional Formatting makes a lot of these functions redundant.
@uvarajsiddhuraj3523 5 жыл бұрын
Nice, clear and valuable.. Thank you 👍😌🙏
@TeachExcel 5 жыл бұрын
You're welcome! :)
@klfa 7 ай бұрын
World best index match tuition. perfect!!
@amandabrown9684 10 ай бұрын
God bless you Watched over 5 videos and yours was the best I tried it and it worked
@georgopoulosgeorge646 6 жыл бұрын
very instructive, thanks!
@TeachExcel 6 жыл бұрын
Glad you liked it! Message me if you have any requests :)
@ashutoshshaw5432 4 жыл бұрын
Wonderful explaination 😊
@amoled5956 4 жыл бұрын
Thank you. This is super easy to understand.
@TeachExcel 4 жыл бұрын
I'm very gald to hear that :)
@markwilson6500 5 жыл бұрын
What do you do if you have two tables with some different items on each and therefore match sometimes returns N/A when you compare the data. I want to find the N/A's and return the serial number and the customer name but the customer name is to the left of the serial number columns?
@MrDadcard 5 жыл бұрын
Could you use a find(-(index)(match formula and would it be simpler? I don't know
@rahulgujar2843 Жыл бұрын
It's great to use Index and match function than vlpookup.Because it's easy to use and worked similar to volookup functions.
@pavannandula 5 жыл бұрын
This video is very nice & useful. I want to know about how to use INDEX function when the array is in 2 different columns. Can you provide some video
@balajimudekulam8135 4 жыл бұрын
Use match function!
@sanjaybucha9012 4 жыл бұрын
Excellent Tutorial
@diamondsandlipgloss 4 жыл бұрын
thank you...i didn't get it initially but those last two examples are usable
@TeachExcel 4 жыл бұрын
glad i could help:)
@azzamabdullah3469 5 жыл бұрын
It can go left right up down all around!! hahaha nice video champ
@Gledii 5 жыл бұрын
Great video! thanks. what about getting a response of 2 or more data in the same line? (in your example, rank+sales+altpart2 to get the part 3. I know one could use concat but would be possibel with index match? I want to search for a value that need to be connected to 3 others In the same line. thanks
@TeachExcel 5 жыл бұрын
So just use the concat method, it's super easy to do and understand in the future when you look back at your formulas. Create a new column and concat the values and then hide that column and reference it in the lookup formulas, where you will concat the input values in the same format.
@KellysOnlineTechTips 4 жыл бұрын
Goede tutorial! Mooie inspiratie voor mijn kanaal!
@sandeshacharya8221 3 жыл бұрын
Understood perfectly with ur explanation
@TeachExcel 3 жыл бұрын
I'm very glad I could help! Thank you for your comment :)))
@2012daffyduck 3 жыл бұрын
Does index match work for extracting information from different sheets within the same workbook?
@taghvaee 3 жыл бұрын
After a while I understand index and match
@KHADIMHUSSAIN-wx1uf 5 жыл бұрын
would be much better if included examples for a layman level
@user-yj2ew9xw1g 3 ай бұрын
thanks a lot i it was very useful for me
@alexrosen8762 6 жыл бұрын
Awesome! Thanks a lot :-)
@TeachExcel 6 жыл бұрын
You're very welcome! :) And let me know what you think about the first part of the video where I explain in detail how to use the INDEX and MATCH functions; I was worried that I spent too much time talking about that.
@alexrosen8762 6 жыл бұрын
TeachExcel I think it was a very good and useful tutorial. Maybe you should have a second part where it gets more advanced and use some real life exemples. Best regards/Alex
@TeachExcel 6 жыл бұрын
Thanks for the input! Sometimes I'm not sure how much time to spend on 'easy' topics so it's good to get some feedback.
@knowledgesharing4574 5 жыл бұрын
Can i find out result in a same column. Like, I want to find out Xyz-9 of Xyz-11
@TeachExcel 5 жыл бұрын
I'm not quite sure what you mean, but you shouldn't search the column that your search formula is in unless you don't include the search formula. If you need more specific help, please upload a sample file on our forum and ask us: www.teachexcel.com/talk/microsoft-office?src=yt
@tacijjola Жыл бұрын
Thank you.
@beenay18 6 жыл бұрын
how to look up for a value which is greater than 3 but less than 15?
@TeachExcel 6 жыл бұрын
Hi, please ask questions like this in our forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
@Entrepreneur-jx3ll 6 жыл бұрын
What would the index and match formula be for looking for the 1st, 2nd and 3rd largest sales, including duplicates, for corresponding Days Since Last Sale and the answer would be the Product #: Example - Product Sales Days Since Last Sale Product #1 58 10 Product #2 65 22 Product #3 47 10 Product #4 65 15 I need the formula to incorporate that Product #2 is the largest Sales (1st Place) and it has been 22 days since last sale and that Product #4 will appear as the 2nd Place for largest sales with 15 days since last sale. Can you help?
@TeachExcel 6 жыл бұрын
Yea that's not difficult at all but go ahead and ask this in our forum! www.teachexcel.com/talk/microsoft-office?src=yt
@hashifpa7259 3 жыл бұрын
Excellent
@marshallhosel1247 2 жыл бұрын
Helpful
@TanvirMostofa Жыл бұрын
I am facing some problems with the index match function (VBA macro excel)
@e.o.8470 5 жыл бұрын
index+match formula is useful , but if the array is uncontinuously cells, shall the array how to be written ?
@TeachExcel 5 жыл бұрын
Depends on a lot, go ahead and upload a sample file to our forum, along with your question, and we'll see what we can do. www.teachexcel.com/talk/microsoft-office?src=yt
@htayhtaywin2069 Жыл бұрын
Thanks
@WenSaiPanther 5 жыл бұрын
I haven’t seen anyone use INDEX/MATCH for dates to look up data. Do you have a tutorial for that?
@TeachExcel 5 жыл бұрын
I don't think so - I'll add it to the list of tutorials to-do.
@bhuvaneswarisrinivasan1004 Жыл бұрын
I have raw data based on that i need to take report in column headers as name1, name 2 till name 10. In rows some cites names are there. Now how to take the report based on the INdex-Match formula
@AllGroundsCoffee 6 жыл бұрын
Will this Index Match function work if Part "asc-4" appear twice in the same data"?
@TeachExcel 6 жыл бұрын
It will work but, in it's current form, only return one result.
@readergirlmn 5 жыл бұрын
Do you have a video that shows how to do that? If "asc-4" appears twice and I want the sum of the sales for both rows?
@WayneRigley 4 жыл бұрын
OMG so lost, im 4 Hrs in to testing and 98 videos in on youtube and still no further ahead. lol im trying to help a guy at work that runs a lottery pool for checking numbers on the sheet. All im trying to do is highlight the numbers in the group on the left that match the 7 numbers in the winning row on the right. any help or direction ? thanks in advance
@merguvan 4 жыл бұрын
How about using a vlookup formula in conditional formatting
@sonjathomas3971 5 жыл бұрын
Who is teaching the tutorial? It's excellent. I think it Tim Wilson.
@TeachExcel 5 жыл бұрын
Glad you like it :)
@hp3kguy 2 жыл бұрын
I have 2 worksheets both contain Credits (+) and Debits (-). I'm trying to find a way to reconcile these to worksheets by finding a credit that matches a debit. i've tried Index/match and it doesn't work. It has problems with the minus sign in the debit column. i.e wksheet 1 wksheet 2 Cr DB Cr DB 34.92 -34.92 (can't find a match because of the negative sign. Any solutions would be appreciated. I've spent too much time trying to create a formula; I'm back to manually comparing columns. Very time consuming. Thx El Bee
@adarateranroldan 2 жыл бұрын
Try using the absolute value, I believe the formula is ABS()
@Dreadnought422 Жыл бұрын
when i do what you say i get #ref
@IronMan_Daddy 4 жыл бұрын
This is a so fucking great video and thanks so much for sharing it. I really appreciate this. you have finally solved my fear about these two functions. you are the man mate!!!!!!
@natashak2125 6 жыл бұрын
What about matching to a number, say 3(from Rank column) and returning the value from Alt Part 3 column, not for 3 but for 7. So this would be an offset Index Match Match.
@TeachExcel 6 жыл бұрын
Then just match for 7 instead of 3?! Otherwise, upload a sample file and question to our forum and you can get much more detailed help: www.teachexcel.com/talk/microsoft-office?src=yt
@natashak2125 6 жыл бұрын
Hello! I have posted my question w/a link to the spreadsheet at TeachExcel.com. Thanks!
@TeachExcel 6 жыл бұрын
You're welcome. I replied there, please see the comment about Editing your question and uploading the file to the question.
@natashak2125 6 жыл бұрын
Thank you, I have uploaded my file!
@jefrinj.k9924 4 жыл бұрын
goodone
@sattarkhan123 4 жыл бұрын
How to select a range of data for index match command on conditional basis. data is provided. please see if you can help. drive.google.com/file/d/1R9RK0WxyF8BHbsGA7Z3eLk0olpe7rgsg/view?usp=sharing
@katalina007az 4 жыл бұрын
As good as the tutorials are, the constant ad interruption breaks concentration.
@huseyinburakbaser9930 6 жыл бұрын
g
@karthikkanigiri7549 Жыл бұрын
Why the he'll every one apply formulas in the same sheet. Please apply this 6 different ways in different sheets or different excels
@richardmelville5534 5 жыл бұрын
Sorry... the video is too blurry to understand
@TeachExcel 5 жыл бұрын
I think it has to do with the connection or video settings - I record all videos in HD. Try clicking the "gear" icon and putting the Quality setting on 720P
@WenSaiPanther 5 жыл бұрын
The one thing about these tutorials that annoy me is that the lookups are always typed into the formula in their singular example, so I have to wait for the pragmatic at the end. Who looks up 1 thing only? It’s confusing because you show one result then go to the next example. One example, one result means NOTHING to me. And you never show any real-world examples. Showing a formula but not showing how to use it is like telling me table salt is Na-Cl and not saying that it makes food taste good. Teach as a user showing a user.
@ericerickson4876 6 жыл бұрын
Poorly thought out tutorial.
@TeachExcel 6 жыл бұрын
What do you think I should do differently in the future?
@TanvirMostofa Жыл бұрын
Dear sir, I am facing some problems with the index match function. But i can not explain.I want to share an excel file to describe . I need help. Please help me . If you want to help me, please send your mail address to share the file.
@FelPacs 2 жыл бұрын
I learned from this tutorial and i watched 3 times just to learn from this video because my work now requires more excell functions just to make my worksheet easy and faster..really thanks a lot from this tutorial..I will watch some of your video to learn more functions in excell. Have a great day ahead