6 Incredible Excel INDEX/MATCH Lookup Examples - Workbook Included
Vložit
- čas přidán 27. 02. 2018
- 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.
That is actually the best explanation of not only how, but why to use both types of functions to make a versatile search.
Thanks so much for your kind words)))
This was really good, Thanks! I'd used Index/Max years ago but never this level. Thanks for the refresher and supercharger!
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.
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
As always, great videos! As I was watching it, i was thinking of other ways to use index and match functions.
I'm happy to have your thoughts and feedback on this! Maybe I can do a follow-up video to this one.
Professional & Informative!
Excellent, thanks for your examples
you are a good teacher (Y)
Sir , You are already a Legend ..
Thank you s much for this accurate and exact explanation
Great , yes very helpful. Learnt something new with the last “monster “. Formulas, but great refresher
Glad I could teach you something new :)
Good stuff Bud...really good teaching style... ease to follow... Cheers
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.
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?
Great tutorial. Of course, Conditional Formatting makes a lot of these functions redundant.
This tutorial is great. Thank you!
World best index match tuition. perfect!!
Best explanation I've seen on this topic. Thank you so much
It is easy to understand. Thanks@):-
Such a great lesson !!!
Excellent job. I NOW get how that works.
Yea I'm glad it helped :)
Very informative. Thanks
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
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.
Thank you for the great tutorials
nicely explained ..thank you sir ,
God bless you
Watched over 5 videos and yours was the best I tried it and it worked
Nice, clear and valuable..
Thank you 👍😌🙏
You're welcome! :)
Could you use a find(-(index)(match formula and would it be simpler? I don't know
It's great to use Index and match function than vlpookup.Because it's easy to use and worked similar to volookup functions.
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
Use match function!
Does index match work for extracting information from different sheets within the same workbook?
It can go left right up down
all around!!
hahaha nice video champ
Wonderful explaination 😊
very instructive, thanks!
Glad you liked it! Message me if you have any requests :)
Thank you. This is super easy to understand.
I'm very gald to hear that :)
thank you...i didn't get it initially but those last two examples are usable
glad i could help:)
Excellent Tutorial
After a while I understand index and match
Goede tutorial! Mooie inspiratie voor mijn kanaal!
Can i find out result in a same column. Like, I want to find out Xyz-9 of Xyz-11
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
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?
Yea that's not difficult at all but go ahead and ask this in our forum! www.teachexcel.com/talk/microsoft-office?src=yt
Awesome! Thanks a lot :-)
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.
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
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.
Thank you.
Understood perfectly with ur explanation
I'm very glad I could help! Thank you for your comment :)))
how to look up for a value which is greater than 3 but less than 15?
Hi, please ask questions like this in our forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
Excellent
thanks a lot i it was very useful for me
would be much better if included examples for a layman level
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
Helpful
Thanks
index+match formula is useful , but if the array is uncontinuously cells, shall the array how to be written ?
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
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
How about using a vlookup formula in conditional formatting
Will this Index Match function work if Part "asc-4" appear twice in the same data"?
It will work but, in it's current form, only return one result.
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?
I am facing some problems with the index match function (VBA macro excel)
I haven’t seen anyone use INDEX/MATCH for dates to look up data. Do you have a tutorial for that?
I don't think so - I'll add it to the list of tutorials to-do.
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
Try using the absolute value, I believe the formula is ABS()
Who is teaching the tutorial? It's excellent. I think it Tim Wilson.
Glad you like it :)
when i do what you say i get #ref
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!!!!!!
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.
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
Hello! I have posted my question w/a link to the spreadsheet at TeachExcel.com. Thanks!
You're welcome. I replied there, please see the comment about Editing your question and uploading the file to the question.
Thank you, I have uploaded my file!
goodone
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
As good as the tutorials are, the constant ad interruption breaks concentration.
g
Why the he'll every one apply formulas in the same sheet. Please apply this 6 different ways in different sheets or different excels
Sorry... the video is too blurry to understand
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
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.
Poorly thought out tutorial.
What do you think I should do differently in the future?
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.
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