INSTRUCTOR: Hey, do you suffer in difficulty in using the VLOOKUP function in Excel? I'm going to show you a very simple method that's a lot easier to use than VLOOKUP. And you may be like I am and you'll never use VLOOKUP ever again. 

But to take a step back, I post videos from time to time about Excel, about what tips and tricks I've learned over the years that make my life easier in doing data analysis. And one thing I always have noticed is everyone likes my VLOOKUP videos the most, that they have trouble doing it. There's error messages that they don't know how to fix, and it just becomes a real pain to use. 

And really, since I've done that video, I've stopped using VLOOKUP. I use another function instead, which I will show in a second. But why don't I want to use VLOOKUP anymore? So let's just hit on three quick points here. 

It's very inflexible. You have to use it one certain way. The data needs to be structured in a certain way. And if it's not like that, well, then you better do something and fix the data so it works with VLOOKUP, which is not the way the real world works. You need something, a tool that can manage however the data structure that will work better for you. So it's just not a very user-friendly tool. 

Second, it can really slow down your spreadsheet. If you have thousands of rows or maybe many columns across, what happens is the way that the formula is structured, it has to index perhaps many columns. And if you start to have, like I said, thousands, tens of thousands, hundreds of thousands of rows, just making one change, even if it's completely unrelated to VLOOKUP, may actually cause your system, your computer, and your spreadsheet to go much slower. So it's not very efficient use of your computer resources. So it's not a fun tool, or it's not a fun file to have open if you're used to working with large data sets. 

And then finally, it's difficult to comprehend, especially for brand-new users. I think almost anyone that hasn't used VLOOKUP in a while, you almost have to go read again. How does this work? I don't understand it. It's a weird name. It just doesn't make any sense. 

So I've kind of just pushed this all aside, and I do everything I can to avoid VLOOKUP. And I recommend to anyone to stop using it. And instead, what you should be using is a-- two functions, actually, that work together, called INDEX MATCH. 

What I'm going to do next is walk you through a very simplistic example of how you use these two functions to duplicate what VLOOKUP does, but it removes all these other elements I have above. And I really believe that you're going to completely take VLOOKUP out of your suite of tools and completely change your mind over to INDEX MATCH. So let's get over to the example. 

For this example, I'm going to demonstrate why INDEX MATCH is a better version than of VLOOKUP. So what I have here are two tables that would commonly be used to, let's say, use a VLOOKUP to pull data from one that matches another table. So on the left-hand side, or the orange table, this would be an example where maybe I have some data that's invoices. And I have a customer ID in here. Obviously, the customer ID could be repeated. But I don't have the name of the customer. 

But over on the blue table, I have it to where it's a one to one-- customer ID to the full name of the customer. And how can I get that full name over into the right table? And this is where you might use a VLOOKUP. So let's go through what that example would look like. And we'll show some flaws with this. 

So we'll just name this the VLOOKUP column. So we'll show this one on here. So if you know how to use a VLOOKUP, you start typing in the formula. And then you pick up the value, which here is the customer ID. You do a comma. I go look then over at the table array. I'll just put everything from column H and I. 

Then I'm going to say I'm going to want to pull the second column, which is the full name. And then I always like to say an exact match to make sure it's working correctly. And then I'm going to go ahead and hit Enter. And there, right there, it pulls customer ID 1001, who is George Washington. And then I can do the same for the other columns right here. And I see there, it has John Adams, Abraham Lincoln, and then George Washington came back, was another customer. So I'm able to see that. 

So in this very simplified example, VLOOKUP works just fine. However, where problems occur is how VLOOKUP requires you to structure the data in a certain way. As an example, over in the blue table, customer ID has to be the first column. If this was reversed so the name was first, then the customer ID, VLOOKUP would not work in this scenario. So you would have to fix this. 

Another example could be is let's say that this was working, but then I decided that I wanted to add a new column to this table. When I go ahead and insert a column between the ID and the full name, it should break at least part of it here. I don't know why it didn't do all of it. But if I just go ahead and do this, it actually does not have that lookup, because within the formula, it said pull the second column. The second column now is blank. 

And maybe if I put another ID, like maybe, What city does this person live in? that would pull over instead. So if I did this, let's say I decided I wanted to put the city name in here, now accidentally, where I wanted to see the name of the customer, instead, I'm seeing this. So you have to keep the data perfectly structured in order for it to work. So that's another reason why here does not work very well in this scenario. Obviously, I can fix it by just deleting that column. Now it goes back to this. 

So now let's look up the INDEX MATCH formula. At first glance, it might seem more complicated because it's actually two formulas in one. So let's first break down the two components, and then we'll combine them together, how this can work, and how it's a lot easier. 

Actually, first, let's build the match-- so the secondary piece-- first. So it's real simple. You just say equals MATCH. And what are we going to match? We're going to match the customer ID. So once again, just like with VLOOKUP, I'll highlight this field right here. Do a comma. 

And then I have to say, where is the lookup array? In this scenario here, with MATCH, I only need to highlight the customer ID column. So that's what I'm going to do. I'm not going to worry about the name right now. I'm just going to do this one. So I'm going to highlight H and then also do an exact match right here. 

And I see right here that it's coming back with a 2. So you might say, what does the 2 mean? And the 2 represents what row is matching customer ID 1001. It's not row one, because it's including that customer ID right here. So H1 would be row one. And then 1001 would be row two. So that's exactly how this will work. 

So I'll copy this. And when I go down here now to customer ID 1003, it should come back, if I did this correctly, and should say 4. Let's see if it works. There you go. It did work. 

And if I come down here now to customer 1002, Abraham Lincoln, that's going to match row three over in the blue side, because once again, it's counting the customer ID and full name, as that's the first row. And then once again, right here with George Washington, it will be 2. So the MATCH portion is only trying to find what row are we talking about. 

And now we'll look at the INDEX function. So the INDEX is going to say, now let's pull back the name. So now I'm going to pull back-- I'm going to type in INDEX. And it's going to say, what array am I looking at now? This I'm going to have to describe, where is the data that I want to pull over? And in this case, it's going to be column I. I want to pull in the names of the customers. So that's the array. 

And now I have to define, what row number do I want? Well, in this case, row number is row number two. That's what we found in the previous column over here. So I'm just going to select this. And then I'm done with the formula right here. So it's going to say, where are the names and what row? And there, George Washington came back. So as an example here, this is what MATCH is doing. MATCH is returning the row. And then the INDEX is returning the name. 

Now, how can we build this together so-- because mainly, you're probably not going to want to see this 2, 4, 3, and 2 in this table. So what you can do now is actually pull them together in what's known as the INDEX MATCH together. And the good thing is by calling it INDEX MATCH, that's the order of what you'll put in here. So let's put an equal sign here and do INDEX. Do an open parentheses. And then say what's the array. The array is the full name, like we already described. 

And then we're going to say, well, what row number do we need? Well, this is where we have to define the row number. So now we're going to put the MATCH value in here. So we'll type in MATCH, do an open parenthesis. And now we have to do the lookup value. We're going to pick 1001, or the B-- row B2. Now I'm going to do a comma. 

And I have to say, where am I going to try to match 1001? And that's in column H. And then at that point in time, because I like doing the exact, I'm going to do a comma and say exact. And then I'm going to close the parentheses once, close it a second time. And then at that point now, I've built the entire INDEX MATCH formula. 

And when I hit Enter, if I did it right-- I'm going to cross my fingers-- George Washington should show up here. And there we go. And now I can just copy this down. And it's going to bring back all of the names right here. So it's a way of actually being able to combine the INDEX and the MATCH into one formula. So now these two columns are no longer needed. 

And you might remember, before I did an example of if I added in a new column here how it would break the VLOOKUP formula. Let's look at this one, how it does not break INDEX MATCH. There we go. INDEX MATCH here still contains this because Excel is smart enough now to know that now I want to look at my array for the value in column H, not in the second column, which was hard-coded before. 

So this is just a way right here to easily show INDEX MATCH. I won't show the example as well, but I mentioned how the VLOOKUP requires that the ID that you're matching has to be in the first column. The good thing about a function like this is it doesn't matter where it is, because the two things are independent. You can have the customer ID be to the right or the left of the data that you're looking at. And it will still come back just fine. 

Anyway, if you had any questions, maybe you might want to watch this section alone but again. But this is the simple way of applying INDEX MATCH to Excel to find an ID in place of VLOOKUP and why I no longer use VLOOKUP and I only use INDEX MATCH. 

Well, that's it everyone. Thank you very much for watching the video. If you have any other ideas about something you'd like to learn more in Excel or something you're having a problem with, I'm more than happy to put together another video that hopefully I can offer you some advice, because after all, Excel is here to make our life easier, not more difficult. And probably if you wanted to do something, there is a way to make it happen. So anyway, thanks again for supporting the channel. And I hope you have a great day.