If you’re looking for the pound for pound, biggest bang for your buck, insert-trite-stereotyped-expression-here, Most Valuable Excel Function, a function that, when properly used, will dramatically increase your Excel mojo, impress your bosses, and make you look like an Excel pro, then let’s talk about Excel’s VLOOKUP function. Yes, it’s an old-timer as far as Excel functions go. And yes, the INDEX and MATCH combination is superior and more reliable. Regardless, VLOOKUP is still extremely powerful, simple to use, and widely used in many professional/work settings (especially for Excel modeling or data analytics) which means you should at least understand what it does.

VLOOKUP is underused by the average Excel user, despite its power and simplicity. I suspect this is because Microsoft defines VLOOKUP rather cryptically:

Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

What’s an array? What’s a return value? What does it all mean?!

The Basics

Many people use Excel for list-making. I love making lists – don’t most people? I bet you’ve already made at least one list today, or if not today, you’ve definitely made a list sometime this week, even if you didn’t write it down or record it in an app. Mental lists are sneaky and known to proliferate.

VLOOKUP lets you search for specific information in your spreadsheet. In general, VLOOKUP can be used anytime you’ve made a list with more than one column, commonly known as a table. Excel tables are ideal candidates for using VLOOKUP, especially when those tables contain a lot of data (i.e., at least several columns with many rows). Here’s a list (yes, really) of common situations that might benefit from using VLOOKUP:

• Sales revenue and/or gross profit by product, by month, by quarter, by year, by an employee, by a vendor, by a customer, by something you can measure, etc.
• Budget forecasts, especially those with columns that span several months, years, or some period (e.g., each column represents one week, or one month, etc.).
• Product information and specifications, with columns for the cost, price, date of manufacture, date of sale, date of shipping, etc.
• Order information (like a Sales Order), with columns for the customer name, address, product purchased, unit price, number of units, sales price, transaction date, promotion codes used, etc.
• Employee information, such as date of birth, social security number, department, date of hire, date of the last review, date of termination, and so forth.

How It’s Done

For the VLOOKUP function to work with a table, that table should have:

1. a column containing a unique identifier (some way to distinguish that particular row from every other row in that table, like an Employee ID, a Serial Number, an Invoice Number, etc.); and,
2. that “unique identifier” column must be the first column in the table (or, at the very least, that column must be to the left of whichever column has the data you’re trying to find.

Step-by-Step Instructions

1. Arrange your data into a table with columns and rows, where each column represents some “thing” (e.g., a week, a month, an address/phone number/email address, etc.), and each row represents a unique, individual record. In this example, we have an employee information table (like an employee census) with columns for the Employee ID (our “unique identifier”), First Name, Last Name, Full Name, Phone number, and Email address, with each row representing an individual employee’s information. We’re going to use VLOOKUP to find and display the full name, phone number, and email address of specific employees.
2. Once your table looks like mine, type “Lookup value:” (without the quotes) in cell B12.
3. In cells B14, B15, and B16, type “Full Name:”, “Phone Number:”, and “Email Address:”, respectively, again without the quotes. I added a bold border around “Lookup value:” and the adjacent cell, but otherwise, your spreadsheet should now look something like this:
4. In cell C14, enter the formula =VLOOKUP(C12,B4:G10,4,FALSE).
5. In cell C15, enter the formula =VLOOKUP(C12,B4:G10,5,FALSE).
6. In cell C16, enter the formula =VLOOKUP(C12,B4:G10,6,FALSE).
7. Now back in cell C12, enter any one of the Employee IDs from the table above, and press Enter.

The Results

The full name, phone number, and email address for the employee whose ID you entered in cell C12 should appear in cells C14, C15, and C16 (where you entered the three VLOOKUP formulas). Want to see a different employee’s information? Enter a different Employee ID in cell C12 and press Enter.

With a little setup work, you can have Excel find and display data from large tables in all sorts of creative ways using VLOOKUP (as I’ll show below). Can you think of other situations where this function might come in handy?

The Details

The Formula

Let’s take a look and try to understand the syntax of the formula itself: =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).

VLOOKUP has three required arguments, or inputs, and one optional argument:

• lookup_value what do you want Excel to lookup or find in the list or table? This is the lookup value. In our example above, we’re using a cell reference (cell C12) to tell Excel what value we’re searching for, but you could just as easily use text (enclosed in quotes) or a number. Excel will look for a match to this value in the leftmost column of our lookup table. This argument is required.
• table_array where should Excel look? This is the lookup table. In our example, we’re using a range (B4:G10) to tell Excel exactly where to search for the lookup value. This argument is required.
• col_index_num which column in the lookup table contains the search result? How do you know what number to enter here? Simply count over from the first column in the lookup table, starting with 1. In our example, starting with column B (Employee ID) as 1, column C (First Name) would be 2, column D (Last Name) would be 3, column E (Full Name) would be 4, and so forth. That’s why the formula we entered into cell C14 has a 4 as the col_index_num – we’re telling Excel to look in the 4th column to find the Full Name associated with the lookup value we enter in cell C12. This argument is required.
• range_lookup do you want an exact match to the lookup value, or is an approximate match okay? If you want an exact match, enter FALSE or 0 here. If you’re okay with an approximate match, enter TRUE or 1. This argument is optional but will default to TRUE or 1 if you leave it out. I usually want an exact match, so I typically enter FALSE here. If you do decide that an approximate match is okay, sort the leftmost column of the lookup table in ascending order to help Excel pick the best approximation.

How and Why It Works

Let’s take a look at the specific formula we entered into cell C14, which is where we wanted the employee’s first name to appear:

=VLOOKUP(C12,B4:G10,4,FALSE)

We’re basically saying to Excel, “Take whatever value I enter into cell C12 (the lookup_value) and search the leftmost column of cells B4 through G10 (the table_array) for a match to the value in cell C12. When you find a match, display the corresponding First Name (the col_index_num, which is the 4th column over), and only give me an exact match (the range_lookup).”

If Excel can’t find an exact match, you’ll see a #N/A error.

Practice Yourself

Earlier in this post, I said I would show some creative ways to use VLOOKUP. Download the Excel workbook I used to show the methods described in this post to see what I mean.

I created a sample Employee Compensation Letter, which uses the Employee ID entered into cell J8 to populate the employee’s name, email address, phone number, salutation, annual raise %, annual bonus %, current salary, annual bonus dollar amount, annual raise dollar amount, and new salary information, all of which was pulled from the Employee Data Table worksheet using VLOOKUP formulas. Try changing the Employee ID to see how the corresponding data in the letter changes. Neato, right?

Feel free to use this template to suit your particular needs, or to practice.

Good hunting!