Hey, better sheet members. This is a fun video because I get to explain why I love Index Match Combo. If you are already familiar with the Index Match combo, I don't know if this will have any more information for you, but it is really for those people who are looking for an easy way to get any info from.

Where in a table. So what I'm gonna show you first is how to use V lookup. I'm gonna explain how that works, what you need to do in order to use it. And then we're gonna jump into index match and I'm gonna show you how it matches up exactly how you can use exactly index match to do exactly what you need to do in V lookup.

And then I'm gonna show you a few things you can do with index match combo that you cannot do with V Lookup or rather. It gets really hard to do a V lookup, and by all means, when I need to do a v lookup, I use it. But I've used index match because it absolutely helps me. What is it? It helps me imagine what I'm doing with that, in that formula and do it much.

Easier. Like it, it helps me figure it out, as I'm writing it, I understand what I'm doing with V Lookup. It's a very simple I wanna say it's a very simple formula once you understand how it works and why it only works that way. And then actually, if V lookup, you can also use H lookup.

I've used it like 100 times less than V lookup, but I have used H lookup and it is actually a little helpful to understand how V lookup works so that you can understand how H lookup works. But honestly, Index match, I use a hundred times more than v lookup. And now I discovered like fairly recently in my life switch data switch.

And that switch formula actually accomplishes a lot of the use cases that I was using V lookup for. If you have a. Set of data, like literally like a little ta like this is a big table here. This is like you have some sort of users or maybe people or salesmen or something. You have all their data in this sort of set.

Maybe this is like a monthly sales data. But if you have, say you want to just switch between, change information from M to a Monday, t to a Tuesday, and it's only 14 points of data where it's seven letters and seven words switch is going to be much easier to use than a V lookup table you have to create somewhere.

Okay, but that's not the topic of this video. This video is all about v lookup and index match and light. It's better we'll get to that in one. Okay. Like I said, the first part of this video, I'm just gonna explain the lookup so that you can hear it from me and how I explain it. If you read the data on it or read the docs on it, sometimes it's a little difficult.

So you have a table you have say these names and these are all unique names. And you're trying to find, like in this column, what is polly's? And you might want to create like a dropdown menu here of people and you just wanna select this first number, or you honestly wanna get however many numbers you want, what's the first column?

This is say a set of unique IDs or a set of unique information that you can say, okay. I don't need to know that Dexter is on row four. I just need to know Dexter. It doesn't matter where he is. In fact, I can actually sort this row sorry, this column and sort this table in any way I want.

But Dexter, his name stays the same. He'll always be the same. So over here I made a little dropdown menu. Here we can select Dexter and you can already see it changes here. And what VLA Lookup is doing is saying what's the name? And that's what's in J three. Okay. So the first thing we need is just what are you for searching for?

What is that key or unique thing that you are looking up? What is the value or what is the thing you're looking for? Just tell me that's the first thing that J three, this first argument. The second argument is what is the table you're looking at? What is the total entire table you're looking at?

And now you see it's all highlighted here. It's purple, so it's from a one all the way down to G 23. Really? We just did A to G. Now here's where it starts getting interesting. This third argument, this two is an index and it's saying, okay. We as Google Sheets in inside of Google Sheets, we know absolutely they're in A to G.

There's 1, 2, 3, 4, 5, 6, 7 columns. We know this a hundred percent. Google Sheets knows this and this number here is saying which of these columns do you want us to tell you the value? What is the value? WH and where, right? And so where we're looking is for J three. So J three, it goes to Dexter, and it's always in the first column.

V lookup is very specific. Here you are looking up whatever is in the first column of the table, and then you're saying, okay, of A through G. What's the second column? That's B. So now we're looking for Dexter in the column. First column, always. And give us what's ever in the second column where Dexter is.

So that's 3, 2, 5 4. And that's what we get. 3, 2 54. So again, we can do this again. Let's change it to he. So there's he. So it's saying, okay, same thing. We're looking across everything here. We're looking for he, but give us what's in the second. Right here, 90 19, and that's 90 19. And that's how look up works.

It always is in the first column, it's always looking for whatever you're first looking for in the first column, and then you tell which column of the table you want. Where it is. Okay. So we're always looking in, whatever number is here. We can change this number too. We can say, let's go to five. Now, what it's gonna do, it's not gonna be the fifth column from B, it's a fifth column from the first one.

So it's A, B, C, D, E. So it's E. We're looking for he 3067, and that's what we get 3067. It literally saves us from having to go through here, go to he go to the. Fifth column here, E. It saves us from having to do that. So I really like using it in this particular case when you literally know what you, who you're looking for or what you're looking for, but you just need to switch the column over.

And one note is that the first column of data that you're looking at is always the two index. They're number two, and the second column is three. This A to G. Here we can also change A to G. And if we do A to B, and now you see we get an error if we do three here, it only has two columns. And this reference error is saying V lookup evaluates to an out of bounds range.

Out of bounds says you're looking somewhere that is not within what you wanted to look through. So I wanted only to look through A through B. But you're saying index three, there is no index three, so we have to change A to B to. And we get this or we keep A to B, maybe we a hundred percent know. We're like, okay, we're only looking in A to B.

There is no other number. Maybe there is nothing here. We know we, okay, we messed up on that three. We just changed that to two. And now wherever Mira is, where's Mira? 67, 73 is 67, 73 here. So that is the lookup. Some of the pitfalls you're gonna run into are one, you always have to have the unique thing that you're looking up in column.

That is actually really harsh because sometimes in some interesting situations, we don't care what order they're in. In fact, sometimes we're looking for in this case, we wanna know, say who, who has the max value in column B? Okay, so we go equals max, and we say column B, right? And the maximum number is 11,000.

And so if I wanted to know who got 11,080, I literally have to go to this column, go to 11,080. Okay, it's Sheena. But what if this is on another tab and I'm just trying to make like a leaderboard say, and I just wanna say who is number one? I wanna put their name here, but I can't do that with the lookup.

So that's where index match comes in. I'm gonna take a short pause and I'll be right back and we're gonna go into index match. Okay. Like I said, the next part of this video and right now what we're gonna do is we are going to use Index Match exactly like we used V Lookup. So the third part of this video, what we're gonna do after this is I'm gonna show you how to use index match in a different way than v lookup allows.

So what are we trying to do? Our normal V lookup, it says in this first column, we have a. And we want to get what we were doing in the second column. So we just want to get the number. We know who we want to get. We not, we want to get to Sheena, we just wanna get this number. 3,846. So how we set that up we got the name same here.

I wish we gonna retype this so I can walk you through what we need to do. So we're gonna do equals index. Actually, this is a little. It's a little more easier to say go from match. So the term is index match, and the combo is index match, and that's how we type it in. We're gonna end up with index and then a match inside of it.

But let's start with match first. Match. What does the match do? Match says here, I can even walk you through this match says search key. Remember our V lookup asked for a search key as well? We want a search key and we want to tell, and we want to know what is the range where that could be. What is the search type?

Is it a hard search, like you wanted an exact match or do you want whatever the first maybe match is? This, I always use a zero and the range you are probably familiar with when you, when we did V lookup, so this sounds pretty similar, right? But we're gonna get a different answer. So our search key is gonna be taina and our range is gonna be A to B.

Again this looks very similar to v lookup, right? But the answer is different. Let's see what the answer is. The answer is in a, okay, that's because match only takes one, one column. Oh my God. I'd probably should have not walked into that problem. Okay? If you get a problem here, if you have problem with match and you get this, or match range must be a single rover column, you only wanna know where the answer is in that a range.

So let's go back to RV lookup. RV lookup had this has an answer, right? It's, it knows there's two columns in this range, and we have to say, okay, in the second column, give us whatever is in the, this one, right? But in match, what we're getting is the number seven the number seven is the row. So we go to the seven through 1, 2, 3, 4, 5, 6, 7.

There's Tash. So how does that help us? We just found Te Sheena's on the seventh row. We could have absolutely just gone over here and said, okay, that's the seventh row. Give me whatever's on the seventh row. We wanna do this programmatically. We right. We don't wanna have to go through this and count every single time.

Okay. Dexter's on the second row of this table. Give us whatever's on the second row we wanted. This is doing it programmatically and now we know what row. And so we just want whatever is on the second column in that row, and that's where index comes. So index says, where do you wanna search? You can search anywhere.

You can search, but right now we're gonna search in B to B. And let me delete this match. Actually, let me copy it. No, let me cut it. Okay. So B2B now, and if we put seven here in. Second thing we're saying, okay, in reference or column, b2b, we want Rowe, we want row seven and we want column one. Okay?

That is 3 8 46. 3 46 is tashina, right? So now we just need to put those things, two things together. Instead of just typing in the number seven. Here we can pace back that match and now, Index match here. I know this thing's really complicated when you're like the lookup can just do this when one thing, why do we need to use index and match?

But wait a second, let's get through this and I'm gonna get you to why this is so cool in the third part. But check this out, right? This acts exactly like the lookup does, right as we change Mira Mountain. 6 7 73, 6 7 73. Exact same answer. And we can also, let's go back to the lookup and let's change this column to let's go to back to G.

So we're looking across everything here. And let's go to column four. Okay. Mira Mountain 2020. Mira Mountain, where are you? So you're in the fourth column here. And if we wanted to do that in index. We would have to go, same thing, mirror Mountain, but instead of B2B, we go D to D and 2 20 0. So again, index match can absolutely 100% do exactly what V Lookup does.

But instead of numbers for the index, we have to know the actual column. So what's interesting here, some of a couple of things that could potentially go wrong is if you're not using the entire column and you do a say a three there, a three here and D three. So Mere Mountain is, see this is wrong now.

So we changed that a column of match and it's like giving us this 67 70. Why? It's because, There's no error here, right? Because it's doing exactly what we asked. We asked, what is this? This match is going to be, let's see if it gives it to us. Nope, you're not giving. Okay that's the number of the row.

But see, over here, these top are mismatched. So if we just change this D to D three, now it's giving us the exact answer, right? The right. So that is one pitfall you can do that'll will not give you an error sometimes, right? The lookup will say out of range or out of bound, or you only really say your range once index match.

You do have to remember that these two ranges have to match in some way, at least in the height. This actually, there are very few, very few use cases where these would be different, but I will now. Get to why index matches so cool. So first off, the very first reason it's cool is that there is no abstraction over the columns.

I physically have to type in here, D to D and I have to physically know that the original one is an eight A. Okay? With the lookup, right? You have to know the whole range. You have to know, You have to know that what you're looking for is in the first column, and you have to have a number of which column is the answer in.

But in index match, again, it's just like a cognitive thing. We all learn in different ways. We all take in information in different ways. For some reason, index match. Is exactly how I think of what I wanna pull out of this table. Okay? I know that Polly, the names are in column A and I want to get what's in column D, right?

It's at the, it's at the very top here. D, there is no four here. It doesn't tell me 1, 2, 3, 4. This gets super easy to use as you get larger and larger tables, right? Because you only need to know where is the information you're trying to. In the lookup, you have to literally count the number of row columns that you're looking for.

So if you're looking at, the 25th column, man, that's hard. That is hard to do. Index match, you just put in whatever the 25th column is. Another thing, and which I think is absolutely amazing, and I've used this so many times I've lost count, is that these two columns, a and. Do the A one does not have to be on the left side, and it doesn't have to be the first thing.

We can actually move this around right now and see these numbers aren't changing because these numbers these these ranges are moving as we move. But also we've moved, now Polly over here on the right of the answer, the lookup literally can't. So this is super fun that then let me undo that as well.

When we get to Max, we know the maximum, and so we use index match to get who that is, right? We did this over here we have the, we have 11,080, and that's the max of column B. Now we want to know who has that, right? So we use index match. We go, let's do that same B to B, and as we change columns, okay, and a why is that?

Oh, I know why. Because we, we did this differently. Okay, we gotta write the whole thing. So index, what are we looking for? We want A to A and we wanna match. Let's go here within, we know that is in b2b and we definitely want a real match. And now we have Sheena. Where's Sheena? Is that Max? 11,080.

Sheena Pagan. There we go. So this allows us to get some really interesting information from our table, right? We can now, maybe leaders. And if we had these, maybe these are May, June, July, August September. How do you spell that? October. And we can copy paste this transposed to do a quickly.

And now we want the max of B. We want equals max a C to C. We want max of D to D, right? And now we can get the maximum person, whoever is in the lead without having to sort this and get the first one. We can do this all in here. Index. What do we want? Index A to a same thing here. I wanna match that to, what was it, June C to C and always zero I always use a zero here like a hundred percent of the time.

There are some use cases where you might not need an exact match. I think it's if it's sorted, it gets the first available match. And then we're gonna just do this one more time again, we, this is our leader, our monthly leaderboard, maybe our employee of the month or something. Index. Just for one more time just to show it to you again.

I'm looking for D to D looking for the answer in D to D or not the answer. A to a, oh, I almost messed that up. Looking for the answer in eight to a matching. Whoever got the matching what? This D zero and there we. Ooh, Marlene Hash got both months, June and July. And see, and you can logically do the rest.

In fact, go ahead, try it out. See if you can create a leader board of months. If you u use this. Hopefully this is a fun way to learn how to use index match. I really like it a lot. I've enjoyed using it. I you sit a lot of times. Great way to. Rarely do a lot of really fun little things with tables.

You just can't do with the lookup. If you are used to do using the lookup, then consider using index match even when you do need to use the lookup. Try using index match in a couple of scenarios and see if you can just teach yourself how to use index match in your particular case. If you have any questions about it, feel free to email me directly and I'm happy to answer any questions.

If you have a difficulty figuring out actually how to use it and your, and you think that your use case is useful with index match, feel free to send me your sheet and I'm, and question, and I'm happy to help you through it. Bye.