The VLOOKUP function is a very popular feature when dealing with large Excel folders or databases. It allows the user to quickly find targeted information about a specific item without having to search the entire spreadsheet.
Don’t worry though – this feature isn’t nearly as intimidating as it looks, and it can save a lot of time and allow for more freestyle analysis. Here’s how to use VLOOKUP in Excel.
Understanding the VLOOKUP paths
The VLOOKUP function is divided into four different “arguments” or values that are entered into your function. These define exactly where VLOOKUP gets information from, so while you start the function with the basics =VLOOKUP()the four arguments you put in parentheses will do all the work.
In short, you tell VLOOKUP the value you want to look up, the range where the value is located, the column where the return value is, and whether the return should be exact or approximate. If you don’t have much experience with Excel functions, it might not make much sense. Let’s make it easier by breaking down each argument into how it performs. Remember, think of an example like a staff guide or a class assessment form to see how this might work in real life.
Step 1: Select the first argument.
This is your lookup value, or the identifying information that you will use to retrieve data about a specific rule in a database or directory. This is the space where you enter information such as employee or class IDs, specific names, and so on. You can choose where this lookup value goes, but ideally it should be close to the VLOOKUP for easy analysis and clearly labeled so you always know what to enter.
Step 2: Select the second argument.
This is the range where your first argument, the lookup value, is in the range. For example, if you’re looking for a specific employee ID number, this argument should contain the entire database. It’s easiest to manually click on the very first entry and then drag the cursor all the way down to the last entry at the bottom right so that it includes all the values in the database. For very large databases, manually enter the first item (A2, for example), a colon, and the last item (B5, in this case), like so: A2: B5†
Note that the second argument must always start with the first (leftmost) column in the database or range. This is also why VLOOKUP doesn’t work well with horizontally oriented lists, but that’s rare in spreadsheets.
Step 3: Select the third argument.
VLOOKUP now knows the full scope of the database or table it’s looking for information, but it needs a little more help. Now you need to select the column that the return value is in, also known as the specific item you want when you type your lookup value.
The third argument must be a number, not a column letter. Start counting from the first input column in the list and continue counting to the right until you reach the column with the data you’re interested in (such as employee bonuses or student grades). Enter this number into the function so that VLOOKUP knows what to return.
Step 4: Select the fourth argument.
The fourth argument is a little different: you can type FALSE or TRUE here to indicate whether you want to return an exact match or an approximate match. You don’t need to do this step if you want to end the feature here, but it does have its uses. A FALSE argument returns an error if it cannot find your input value, for example if the employee ID you enter does not exist. A TRUE entry rounds to the nearest result and returns the desired value for that entry, which can simplify certain types of analysis.
Now that your VLOOKUP function is complete, you can now start entering values into your lookup space and see the results that VLOOKUP returns.
Important Notes to Remember
-
VLOOKUP always moves to the right. It won’t go to the left. Keep this in mind when organizing your lookup data.
-
VLOOKUP does not understand duplicates. For example, if two employees have the same last name, VLOOKUP just stops at the first one on the list, whether it’s the name you want or not. Therefore, the function is often used with full names or ID numbers.
-
VLOOKUP is case sensitive, so it can tell the difference between looking up a capitalized word and a word that isn’t.
-
Like other Excel functions, it’s easy to extend VLOOKUP to an entire table to return multiple values at once, depending on your project. Once you’re comfortable with the process, you can start using it in more complex ways!
Editor’s Recommendations
