6 Incredible Excel INDEX/MATCH Lookup Examples - Workbook Included

[@RollinShultz]

That is actually the best explanation of not only how, but why to use both types of functions to make a versatile search.

[@TeachExcel]

Thanks so much for your kind words)))

[@FelPacs]

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

[@klfa]

World best index match tuition. perfect!!

[@xxczerxx]

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.

[@amandabrown9684]

God bless you Watched over 5 videos and yours was the best I tried it and it worked

[@mahmoudhassan6627]

Sir , You are already a Legend .. Thank you s much for this accurate and exact explanation

[@big1975E]

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.

[@mkmstillstackin]

This was really good, Thanks! I'd used Index/Max years ago but never this level. Thanks for the refresher and supercharger!

[@tommyharris5817]

Great tutorial. Of course, Conditional Formatting makes a lot of these functions redundant.

[@rahulgujar2843]

It's great to use Index and match function than vlpookup.Because it's easy to use and worked similar to volookup functions.

[@keifer7813]

Best explanation I've seen on this topic. Thank you so much

[@taghvaee]

After a while I understand index and match

[@sandeshacharya8221]

Understood perfectly with ur explanation

[@TeachExcel]

I'm very glad I could help! Thank you for your comment :)))

[@leoncarr920]

Good stuff Bud...really good teaching style... ease to follow... Cheers

[@markwilson6500]

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?

[@pavannandula]

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]

Use match function!

[@johnwatkins39]

As always, great videos! As I was watching it, i was thinking of other ways to use index and match functions.

[@TeachExcel]

I'm happy to have your thoughts and feedback on this! Maybe I can do a follow-up video to this one.

[@diamondsandlipgloss]

thank you...i didn't get it initially but those last two examples are usable

[@TeachExcel]

glad i could help:)

[@azzamabdullah3469]

you are a good teacher (Y)

[@amoled5956]

Thank you. This is super easy to understand.

[@TeachExcel]

I'm very gald to hear that :)

[@kdumm7563]

Excellent job. I NOW get how that works.

[@TeachExcel]

Yea I'm glad it helped :)

[@TanvirMostofa]

I am facing some problems with the index match function (VBA macro excel)

[@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

[@NotSherine]

Great , yes very helpful. Learnt something new with the last “monster “. Formulas, but great refresher

[@TeachExcel]

Glad I could teach you something new :)

[@leoburnett9049]

This tutorial is great. Thank you!

[@2012daffyduck]

Does index match work for extracting information from different sheets within the same workbook?

[@ChamanAli-v4i]

thanks a lot i it was very useful for me

[@sanjaybucha9012]

Excellent Tutorial

[@Gledii]

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]

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.

[@gjmarsh8787]

Professional & Informative!

[@johnkaggwa7903]

Thank you for the great tutorials

[@engelberthk]

Excellent, thanks for your examples

[@banwarifageriaexcelclasses1447]

nicely explained ..thank you sir ,

[@hp3kguy]

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]

Try using the absolute value, I believe the formula is ABS()

[@ashutoshshaw5432]

Wonderful explaination 😊

[@tacijjola]

Thank you.

[@uvarajsiddhuraj3523]

Nice, clear and valuable.. Thank you 👍😌🙏

[@TeachExcel]

You're welcome! :)

[@GeethMilan]

Such a great lesson !!!

[@hashifpa7259]

Excellent

[@marshallhosel1247]

Helpful

[@MrDadcard]

Could you use a find(-(index)(match formula and would it be simpler? I don't know

[@htayhtaywin2069]

Thanks

[@KellysOnlineTechTips]

Goede tutorial! Mooie inspiratie voor mijn kanaal!

[@knowledgesharing4574]

Can i find out result in a same column. Like, I want to find out Xyz-9 of Xyz-11

[@TeachExcel]

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

[@mistycarter9396]

Very informative. Thanks

[@george.geo1]

very instructive, thanks!

[@TeachExcel]

Glad you liked it! Message me if you have any requests :)

[@sonjathomas3971]

Who is teaching the tutorial? It's excellent. I think it Tim Wilson.

[@TeachExcel]

Glad you like it :)

[@WayneRigley]

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]

How about using a vlookup formula in conditional formatting

[@Entrepreneur-jx3ll]

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]

Yea that's not difficult at all but go ahead and ask this in our forum! www.teachexcel.com/talk/microsoft-office?src=yt

[@Markd4Comment]

I haven’t seen anyone use INDEX/MATCH for dates to look up data. Do you have a tutorial for that?

[@TeachExcel]

I don't think so - I'll add it to the list of tutorials to-do.

[@AllGroundsCoffee]

Will this Index Match function work if Part "asc-4" appear twice in the same data"?

[@TeachExcel]

It will work but, in it's current form, only return one result.

[@readergirlmn]

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?

[@beenay18]

how to look up for a value which is greater than 3 but less than 15?

[@TeachExcel]

Hi, please ask questions like this in our forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment

[@e.o.8470]

index+match formula is useful , but if the array is uncontinuously cells, shall the array how to be written ?

[@TeachExcel]

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

[@lemon-vk7wq]

It is easy to understand. Thanks@):-

[@Dreadnought422]

when i do what you say i get #ref

[@KHADIMHUSSAIN-wx1uf]

would be much better if included examples for a layman level

[@azzamabdullah3469]

It can go left right up down all around!! hahaha nice video champ

[@alexrosen8762]

Awesome! Thanks a lot :-)

[@TeachExcel]

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]

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]

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.

[@natashak2125]

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]

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]

Hello! I have posted my question w/a link to the spreadsheet at TeachExcel.com. Thanks!

[@TeachExcel]

You're welcome. I replied there, please see the comment about Editing your question and uploading the file to the question.

[@natashak2125]

Thank you, I have uploaded my file!

[@IronMan_Daddy]

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!!!!!!

[@jefrinj.k9924]

goodone

[@katalina007az]

As good as the tutorials are, the constant ad interruption breaks concentration.

[@sattarkhan123]

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

[@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]

Sorry... the video is too blurry to understand

[@TeachExcel]

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

[@huseyinburakbaser9930]

g

[@Markd4Comment]

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]

Poorly thought out tutorial.

[@TeachExcel]

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]

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