How to use Excel Index Match (the right way)

How to use Excel Index Match (the right way)

Articles Blog


In this lecture, I’m going to show you how
you can use index and match to solve complex lookup problems. The thing with INDEX and MATCH is that it’s
not a VLOOKUP, it’s much better than a VLOOKUP. And you are going to come across situations
or you’ve probably come already across situations where VLOOKUP just wasn’t working. It couldn’t do the lookup that you wanted,
because your lookup problem was too complex. That’s exactly when index and match can come
to the rescue. It was difficult for me to start using index
and match. Just like a habit, I had to force myself at
the beginning to use it until I got the hang of it. Now, what I’m going in this lecture is first,
to explain to you how index works in easy terms. And then I’m going to show you how match works. And then we’re going to put these together. So, the example I have is list of divisions,
apps, revenue, and profits. The aim of our formula is that we want someone
to select an app here, so let’s say Misty Wash and we want to get the division first. So you can see that the order of these, apps
is here, division is here, right? Would VLOOKUP work? The classical VLOOKUP is not going to work,
right? Because you will need to have apps on this
side and division on this side. That’s why index and match is great for this. Let me show you what index does on its own,
alone. The first argument in index is the array argument. Think of it like this: INDEX is like a GPS.
For this GPS you need to upload a map on there. Your map is your array. Okay, so if i highlight this, that’s my map. And what map do you give it? Well, the only map it needs is the map that
has your answer in it. It doesn’t matter what your lookup problem
is, it doesn’t matter in this case that we’re looking for an app and it’s called Misty Wash,
I don’t need to include that in my map. I only need to include in the map where my
answer is. If my answer was also going to be here or
here or here, I have to extend my map. But in this case, I know that I wanted division. And the division is somewhere here.
That’s all I need to include. Okay, the next argument is basically how many
rows do you need to go down, and how many columns do you need to move across? Think of it like the longitude and latitude
in a map. And these arguments are numbers that you give
it. If I say move down two rows. I close the bracket, because the last argument,
you see it’s in square brackets, it means it’s optional; it’s not necessary. And in this case anyway, I just have one column,
so I’m going to put two. Okay, I get Game. Why? Well, I indexed what? This area, right? And it counts like this: This is a one, this
is a two. If it returns the second place, and that’s division. Well, what happens now if I put one in there
and I close the bracket? It’s still Game. It’s one column, right? If I put a zero, what happens? It’s still Game. Excel realizes that it’s one column. But what happens if I put a two here? Reference. I’m moving outside my map. Okay, if I was going to do that, if I really
think that my answer is actually somewhere here, all I have to do is extend my
map. Instead of A6 to A15, I’m going to look until
B15 and then it works. That’s all there is with index. Now, the part that we want to automate … Now,
obviously we’re not going to input two and ones as the numbers here. The part that we want to automate is the two. It’s this row number argument. This is where you need a function that is
going to return a number to the index. Which functions return numbers? Let’s think of a few. You have the COUNT function, right? You have COUNTA. You have the have the row, you have the column functions. Sometimes you could use these as arguments
in the index function, but in most cases, the function that works in harmony with INDEX, that you’re going to need, is the MATCH function. Let’s just write here… and see what match does on its own. Match needs a lookup value. What is looking for? In this case, we’re looking for Misty Wash. And it needs to lookup array. Where should it find this? In this case, it’s here. One thing you need to watch out with the match
function is that it needs a one-way street. You cannot give it something like this, because
then it doesn’t know should it look this way or should it look this way? It has to be a one-way street. Let’s go back. That’s where it should find it. And then the last argument is the match type. Do you want an exact match, less than, or
greater than? In most case, you’re going to need an exact
match. That’s like the false argument in VLOOKUP. If your data was sorted and you were looking
for an approximate match, then you’re going to need less than or greater than. But, majority of the cases, it’s going to
be zero. What am I going to get? Nine. What does that mean? That means that Misty Wash is the ninth position in here. Is is the ninth position? Yes, it is, right? That’s exactly the argument that we’re going
to return to the index function. Let’s type this now, the full formula. First, what comes in the index argument? Where we think the answer is, right? The map that contains the answer. And that’s that. What is our row argument? Well, we’re going to use match to figure it
out for the index argument. And we’re going to match this one. Where? In here, and we’re going to look for an exact
match. Bracket close two times. Now, the only important thing here is that
I have the same length, the same array length, for both my index and the match functions,
because they need to be in sync, right? And this gives me Utility. Because if they’re not, I’m going to be returning
the wrong address to the index function. Now we’re going to do the same thing for profit. We’re going to index. What should I index right now? This column, right? That’s all I need. And how many rows should I move down? I’m going to use the match function. What am I looking up? I’m looking up Misty Wash. Where am I looking it up? Well, only in here. Okay, arrays have the same height. And I’m looking for an exact match. Bracket, close, close. And that’s my number. That’s a simple INDEX and MATCH, but what if I wanted
to switch between profit and revenue here? Let’s do something. Let’s add a validation to this. I’m going to put data validation, list,
and I want these two. Here what I want to do is to be able to switch
between revenue and profit, and this number should obviously change. How do I do that? That’s when I need to use the column argument,
right? But, is that the only thing I need to add,
or do I need to update something in my original map in my index? I have to update my map, right? Because my map now should also include the
revenue column, because my answer could be somewhere here, could be somewhere here, depending
on what the user’s going to select in the dropdown. First thing is to update the map. The second thing is what about the row argument? Is that okay? It’s fine, right? Because I know I should move down this many
rows. And then the next question I need to answer
is how many columns do I move? Well, what does that depend on? It depends on what the user has selected. I’m going to match again, because I need a
number back, right? I’m going to match again for this, that’s
my lookup value. Where am I looking this up? In here. And you see this range, the width of my lookup
array is the same as the width of my index. I have to be in sync. And then I’m going to get a perfect match,
close this. I think that’s it. And click enter. Now, what happens? I go for revenue, I get revenue. I change this to, let’s go to Hackrr. Hackrr is a game. It has this much revenue. And how much profit? This much profit. That’s how you can use index and match for
matrix type of lookups. What I suggest you do is that the next time you
come across a lookup issue, don’t use VLOOKUP, even if VLOOKUP will work there. Try to use index and match, because that’s
the only way that you’re going to get practice. And the more practice you get, what happens
is that then the moment that you get a more complex lookup, let’s say your colleague is
trying to do a VLOOKUP and it’s not working and they ask you, “Do you know how to solve
this?” And you’re going to be like, “Yes. I’m going to use index and match here.” In the next example, I’m going to show you
how you can use it to solve more complex problems, because in real life, you don’t have your
data generally set up as simple as this. You might have it set up like this where you
have more than one header. And we’re going to see in the next lecture
how to solve this. I hope you enjoyed this lecture and don’t
forget to subscribe to get notifications when I put out more videos that are like this one.

100 thoughts on “How to use Excel Index Match (the right way)”

  1. Great video! I have gotten this to work on sheets in the past, but I always have to fiddle around with it because I don't do it enough. This is a very clear explanation of how it works!

  2. I used index and match where I kept index array as a table of multiple columns and column as a fixed number from where I want to get the result … But this is amazing .. more intelligent usage …

  3. These are some of the best down-to-earth explanation videos I have seen. You don't just rush over it, expecting your audience to know everything already and divide the task in steps. However, at the same time you don't talk slow where it might lose the attention of the audience. And of course, the way you explain it is very intuitive and therefore easy to understand!

  4. Hi! Leila, can we use, COUNTIFS formula with some condition….like how many "Yes" for "A" in entire table array. Please suggest.

  5. Hi Leila. Thank you for your tutorials, I have learned quite a bit and has improved my quality of work at the office. I do have a question that I'm hoping you could provide a solution to.
    In cell A1 I have ABCdef123 – this is for a Red Shirt
    In cell A2 I have abcDEF123 – this is for a Blue Shirt

    Is there a way for excel to differentiate between the data in Cell A1 and A2 individually and not read them as one in the same or as duplicates? I'm trying to pull up a master list to make changes to the RED shirts only but blue shirts are coming up with it as well.

    Thanks for all you do in the Excel and YouTube community!

  6. How can I use index match to pull multiple columns of data for one item? For example, I have a purchase order with about 30 unique SKUs. I also have a sales report with about 300 unique SKUs along with several columns worth of cost and profit data. I want to be able to paste the SKUs from the purchase order in a separate excel tab and pull the corresponding sales data for just those SKUs.

  7. Thank you! this was very helpful! it's not easy to understand these mixed together. I like your concise approach. Keep it up

  8. Hello madam how are you, I am mostofa kamal form Bangladesh, how can I connect with you, my some help about database

  9. Hello! I have one Question, The formula working good. but if we use index and match in which Match array has two similar variable than always point to first found colums . for Eg table with 2 colums product, quantity… Product A,B,C,D QUANTITY 8,6,6,5 for qnty 8 it show product A but for 6 it always show product B .
    I do not want to go to worksheet and manually sorts

    I want to use this function auto sorting top 10 highest quantity product that i have… it two product have same quantity it show only first found match.

    I appreciate you comment

    Hassan Bhimani

  10. It's not that hard but I keep getting confused about when to Index and when to use match. I guess I have to practice a bit more to get better. Thanks for the video

  11. You have saved me at my job multiple times, thank you for being a force for tech women in the making 🙂

  12. Wonderful video blond girl. In fact, I would suggest the viewers to watch your video on OFFSET after watching this INDEX & MATCH, for the simple reason, the concept /analogy / context of moving rows and columns is more or less the same. This way one can learn three functions in far less time 🙂 Cheers !!

  13. Leila, You are awesome. I am in love with your teaching style. So easy and meaningful as well as the best way to make people/student understand. God Bless you!

  14. Thank you so much Leila for teaching me Index Match in such nice manner & way. It was taking a long time to complete any look up issue with the V Look UP and now I have started to believe that this formula will surely save a lot of time. Thanks again. You have been very popular for your teaching style. May you continue to teach us in this fantastic way…Amlan.

  15. Hi Laila. I have a question pertaining to ppt. how can I share my quarry as it involves ppt representation and can only be explained by sharing such representation.

  16. in a nutshell, I'm sure you have observed ppl sharing there ppt creative content as a collage (kind of) way. I would like to know how it's done.

  17. Hi
    How to find out more then one occurrence of search criteria occured ? I have client with shops at four different locations.Compnay name is same. Billing address is different. How to find next occurrence ? Can you guide ? I got to know about index match after seeing your video. Thanks for sharing. Same issue I had with vlookup, so was searching solution.

  18. after searching youtube very much I found you. Here I don't need to rewind your video..

    Very respectful Thanks

  19. Fist of all you are awesome the Best Video online . Now my question can I use serch or find function to lookup certin amount of number
    Example

    have a table that has words with numbers on a cell and a diffrent cell on a different sheet has just the number but we need a value on the left side of it

  20. Thanks a lot. So useful. I was restricted to the land of lookup, and you've just given me a passport for the whole world. Long live to index and match.

  21. Good day…. Your teaching on Excel makes help me a lot but can you teach us in making a formula of number but has a automatic words like "discrimination index and difficulty index". Thank you so much I hope you consider this in your video..

  22. Hello Leila, I need your help actually I am stuck in one of project where I have 100 lines and I needed only value which is after "with". Could you please help me out.

  23. Hi Leila, Thank you again for this wonderfull code, i love to use it 🙂 But i got one little issue i can not seem to fix. when i get a #N/A because there is no data in my range, i would like to get rid of the #N/A value and replace it with 0 or nothing. i know how to do this with Vlookup, but this code does not work for INDEX. I really hope you got a solution for me.

  24. YOU ARE AMAZING I could never understand index and match and you not only taught me that but took me a step further!

  25. we dont have time to watch your life story in every video…nobody cares..sorry if i am rude i cant expess kindly in english because i am in b1 level

  26. Great vid! This was perfect, helped me figure out exactly how to use to solve a problem i had in Apple Numbers. So this works for both Excel and Numbers

  27. How can we generate a set of random numbers in a particular range also there total is predefine.
    In randbetween we get random no but total of these no not that i want so every time i have to adjust 1 or 2 random no to total matches with my total value.
    Can you suggest me or make a video.

    For example 1 want 13 random numbers between 15000 to 20000 whose total should be 237000

  28. I really like your explanation! Totally makes sense. My question is, what if I need to pull in a column of data from another worksheet?

  29. awesome and interesting way of teaching . just one point. @ 5 min 35 second in video . i think the reason match cant look a 2 d array is that it cant give answer like (3,2), it has to answer in an number ,

  30. I have a question, at 07:00 in the video when you tried to find the Division of Misty Wash, what if you have wanted division of more than 1 Misty Wash? I know the match function only gives us the value of the 1st match but what if we wanted the value of 2 Match too after the 1st match.

  31. This randomly showed up in my recommendations and now i've fallen in love.
    Been working over a decade as an accountant and generally considered as an expert in excel but this wonderful woman shows me things i've never seen before

  32. I have question i am trying to reconcile two name list to find out if everyone in list A is in List B and vice versa. Can i use index and match to reconcile? Can you please help me with this?

  33. Good Evening Ma’am, In excel, one person complete total 8
    working hours means I want to show automatically as a c-off
    if less than 8hours actually working. Kindly guide me,

    please.

  34. Hello, Hope you are doing well.
    I require assistance with the index match formula. The scenario am facing is the formula is providing required output in the first column, but when extending it to next column it's showing out an error. It seems to be an invalid cell reference error. But I have got it checked with known by close people who are relatively good in excel and cell referencing looked good and are unable to confirm or identify the error occuring.Can you please help me with possible reasons for this to occur?

  35. This video is fully self explanatory. Thanks for your effort.

    But I think your front video need not be there. Rather a watermark may be enough for copyright purpose.

  36. SO THIS WORKS PERFECT FOR WHAT IM TRYING TO DO EXCEPT ON THE DROP DOWN I WANT TO BE ABLE TO TYPE LIKE THE FILTER FEATURE…. IS THIS POSSIBLE?

  37. I was always afraid of index -match function.. instead i used vlookup.. now this video helps me a lot.. I would like to thank u for teaching us the most complicated issues with easier way…Lots of Thanks..

Leave a Reply

Your email address will not be published. Required fields are marked *