If you want to go from novice user to highly-respected Excel office guru, VLOOKUP is the function you need to master. It’s hard to underestimate the importance of VLOOKUP, so I can say without exaggeration that VLOOKUP will change your (Excel) life. Knowing it is absolutely essential.
You might be wondering why VLOOKUP matters so much. Fair enough! The briefest summary I can give is that VLOOKUP eliminates repetition by allowing you to easily combine sets of data. Maybe your data comes from different sources. Maybe your data always comes out separated, and you need to keep it together. Whatever the reason, VLOOKUP in Excel can help solve your data woes.
Let’s get started with an example: imagine that you have two tables, one with your employee IDs, names, and sales information. You have another table that lists employee supervisor information, but it’s not connected to your current table.
You could manually plug in all of the supervisor names, but if you wanted to waste your time doing that, you wouldn’t be reading this article! Feel free to follow along with this example. The original Excel file is available here.
First, insert a new column and title it Supervisor. Then, type =VLOOKUP and each of the criteria for the function will appear as shown below.
Let’s go one by one through the criteria:
- lookup_value – This is value you are going to use to “connect” the data. So you need to choose the value in your initial list that will be used to lookup the value in your supervisor list. In this case, you could actually use Employee ID or Employee Name, as those are both unique values in your lists. But what if you have an Employee with the same name? It’s safer to use Employee ID, so click on cell A2.
- table_array – Now, you need to define your table. Think of it this way: VLOOKUP will help you find the Lookup Value in the Table Array. Your lookup value is the Employee ID, so you want to find the match Employee ID in the Supervisor table. Select cells H2:J11. (Pro Tip: you’ll want to make these references absolute so you can drag down the formula to all cells. Do that now by pressing F4 on your keyboard. More to come in the pitfall section.)
- col_index_number – col_index_number is short for Column Index Number (clever, right?). You’ve already told VLOOKUP which Employee ID it should lookup and which table it should refer to. Now you need to tell it which column to return. Your lookup table is only three columns, so this is pretty easy. (One day you’ll have 50 columns… then you’ll be a VLOOKUP pro!) Your first column is Employee ID, your second is Employee Name, and your third is Supervisor Name. Since you want to get Supervisor Name, and it’s in the third column, type 3.
- [range_lookup] – Range lookup is optional in theory, but in practice, always put something here. Excel is asking you whether you want to consider a range of data here, but you want an exact match, so type false. We discuss when you might use TRUE later in the article.
Congrats! Your VLOOKUP is now in cell F2. To extend it to the bottom of the list, click on cell F2, then double click on the square in the bottom right corner of the cell. Check your work against the final workbook here.
Next, let’s learn a few ways your VLOOKUP can go wrong. Trust me, it can and will go wrong!
Common VLOOKUP Pitfalls & How to Fix Them
Now that you know the basics of VLOOKUP, you should know some common VLOOKUP pitfalls:
Not making your cells absolute – When you want to copy your VLOOKUP formula across a column, make sure your lookup table is absolute. You can also name your table array, which is best practice.
You can manually go into the formula bar and put dollar signs in front of all of the cells you want to make absolute, or you can press F4 to do it more easily. For more information, check out our article on The Difference Between Relative and Absolute References in Excel.
Messing up TRUE/FALSE for exact matches – More than likely, you’ll be choosing FALSE for exact matches. But what if you need to get a range of data? Let’s consider.
Imagine that you are a book wholesaler that offer a discount if bookstores buy at a particular quantity. In this case, you actually want to lookup the price for a range of quantities. For quantities 1-9, you offer price $24.99 per book, for 10-24, you offer $22.49 per book, and for 25 & above, you offer $19.99 per book.
Here’s what happens if you choose FALSE:
Clearly, you don’t want to choose FALSE because it returns an error (except for quantity 10, because that exact number is listed!). Remember, Range Lookup is asking if you want to lookup data across a range. In many cases, the answer is no, so you choose false. In this case, however, you do want to look across a range. Here’s what happens if you choose TRUE:
Great work! Now you know how to use a range lookup if you need it.
Your numbers are stored as text – This mistake will drive you crazy for hours! If you are looking up numbers, your numbers must be formatted as numbers. If you are getting an #N/A error in your VLOOKUP, check this right away. Usually Excel gives you a warning here. If you see a little green flag in the bottom right corner, click on the cell and then click on the warning message. Excel will convert it for you if you allow it.
Believe it or not, you are not a VLOOKUP Master yet. VLOOKUP is an incredibly useful function with a variety of uses for combining disparate data sets. The best way to improve with it is to try it with your own data. Hopefully, you’ll fail spectacularly… but then be able to solve it on your own! Magoosh also offers Master Excel: Beginner to Advanced, a great series of video lessons that can help you level up your Excel skills.
Can’t get enough VLOOKUP? Share your questions below, and we’ll discuss!