No video :(

6 Incredible Excel INDEX/MATCH Lookup Examples - Workbook Included

Подписаться 252 тыс.

Просмотров 429 тыс.

50% 1

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.

Опубликовано:

27 фев 2018

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 104

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

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

@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

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

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

@mahmoudhassan6627 3 года назад

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

@leoncarr920 4 года назад

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

@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 :)

@gjmarsh8787 5 лет назад

Professional & Informative!

@tommyharris5817 3 года назад

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

@engelberthk 5 лет назад

Excellent, thanks for your examples

@keifer7813 2 года назад

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

@azzamabdullah3469 5 лет назад

you are a good teacher (Y)

@amandabrown9684 10 месяцев назад

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

@leoburnett9049 3 года назад

This tutorial is great. Thank you!

@kdumm7563 6 лет назад

Excellent job. I NOW get how that works.

@TeachExcel 6 лет назад

Yea I'm glad it helped :)

@klfa 7 месяцев назад

World best index match tuition. perfect!!

@uvarajsiddhuraj3523 5 лет назад

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

@TeachExcel 5 лет назад

You're welcome! :)

@mistycarter9396 5 лет назад

Very informative. Thanks

@lemon-vk7wq 6 лет назад

It is easy to understand. Thanks@):-

@GeethMilan 4 года назад

Such a great lesson !!!

@johnkaggwa7903 4 года назад

Thank you for the great tutorials

@banwarifageriaexcelclasses1447 5 лет назад

nicely explained ..thank you sir ,

@azzamabdullah3469 5 лет назад

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

@rahulgujar2843 Год назад

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

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

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

@diamondsandlipgloss 4 года назад

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

@TeachExcel 4 года назад

glad i could help:)

@MrDadcard 5 лет назад

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

@ashutoshshaw5432 4 года назад

Wonderful explaination 😊

@georgopoulosgeorge646 6 лет назад

very instructive, thanks!

@TeachExcel 6 лет назад

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

@amoled5956 4 года назад

Thank you. This is super easy to understand.

@TeachExcel 4 года назад

I'm very gald to hear that :)

@sanjaybucha9012 4 года назад

Excellent Tutorial

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

@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

@sandeshacharya8221 3 года назад

Understood perfectly with ur explanation

@TeachExcel 3 года назад

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

@KHADIMHUSSAIN-wx1uf 5 лет назад

would be much better if included examples for a layman level

@KellysOnlineTechTips 4 года назад

Goede tutorial! Mooie inspiratie voor mijn kanaal!

@tacijjola Год назад

Thank you.

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

@hashifpa7259 3 года назад

Excellent

@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

@htayhtaywin2069 Год назад

Thanks

@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

@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

@marshallhosel1247 2 года назад

Helpful

@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

@sonjathomas3971 5 лет назад

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

@TeachExcel 5 лет назад

Glad you like it :)

@TanvirMostofa Год назад

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

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

@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

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

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

@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

@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

@jefrinj.k9924 4 года назад

goodone

@katalina007az 4 года назад

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

@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

@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

@huseyinburakbaser9930 6 лет назад

@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 года назад