What’s a VLOOKUP?
VLOOKUP is a well-known Excel formula that can save you hours over performing the same tasks manually. This handy function – as the name VLOOKUP suggests – will allow you to perform vertical lookups to search for specific data. The V stands for vertical, signifying the means by which Excel will search for your data. Vertical lookups are like navigating through a phone book: when you search for a number, you first look for the target name in the leftmost column. Upon finding the name, you will move your finger to the right in order to match the column that contains the phone number, which is the target information. The VLOOKUP function works the same way.
Before we begin, it is recommended that you download the sample workbook, since we will be using it to demonstrate how this functionality works.
Lead by Example
Let’s begin with an example: you are trying to look up the salary of an employee from the sample list. In an abstract sense, we first need to search for the employee’s name in the Full Name column, which will indicate the target row, and then find the corresponding value from the Base Salary column for that row.
Let’s enter these parameters into a VLOOKUP statement.
Excel provides a helpful tool – aptly named, IntelliSense – that will show you placeholder parameter text bubbles to guide you in building a formula. When you start typing “=VLOOKUP” into a cell and add the opening parenthesis, each argument will be highlighted as you add them, giving you a sense of what the formula is looking for.
The first parameter is lookup_value. This parameter indicates what we are looking for. It’s the person we are trying to call in the phonebook analogy, or the employee’s name in this case.
The second parameter is table_array, which designates where we are looking. We need to define the range or table where we want Excel to look for this information. This would be the entire phone book (including all columns) in the phone analogy, or columns A through J and all rows on the Employee Data sheet.
Next, we have the col_index_num. This is the information we are looking for – the phone number in the phonebook analogy, or the Base Salary in this case. This parameter must be indicated as a column index – more on this momentarily.
The final parameter is an optional Boolean ([range_lookup]) and effectively designates whether to perform an approximate search or a precise search. This can be indicated using 0/FALSE or 1/TRUE. If you don’t enter anything for this parameter, it will be 1/TRUE by default. Most often, you will want to use FALSE (i.e. precise) as the approximate option is mostly useful when searching for numerical values. More on this later.
Now that we have the basics, let’s get down to finding the salary of an employee. We are going to be looking for employee “Bates Haley” in this example. The sample workbook contains a sheet dedicated to our search and we can type our formula into the Base Salary box.
Step by step, let’s fill out the formula:
- First, we are going to add the value (or cell reference of the value) that we want to look up (lookup_value). Our first argument is in cell C1, which is where we are going to type in the Full Name of the employee that we want to find.
- Next, we need to add the range or the table reference for our data. Remember: VLOOKUP always searches in the first column of your range selection. In this example, we will select the range as A1:J50 (table_array).
- Excel can now find the name we are looking for. However, we still need to indicate what data we would like returned to us. To do this, we enter the index of the column (col_index_num) which contains the result value in the data range/table we selected. Since we want the formula to give us the Base Salary of this employee, we need to enter 7 (column G). The indexing in Excel begins with value 1 from the left-most column in your range (in this case, Column A). The index increases by 1 for each column to the left (e.g. in this case, B = 2, C = 3, D = 4). IMPORTANT: The column we are searching in (Full Name) needs to be to the LEFT of the column we are searching data in (Base Salary). In other words, VLOOKUP can only start looking for a name on the left, and then return data that is to its right!
- Lastly, we need to tell Excel whether we are looking for an approximation or a precise value. Adding FALSE (or 0) as [range_lookup] argument will work in most cases as it indicates an exact search. We suggest using FALSE unless the first column of your data contains sorted numerical values (especially if they are formula calculated, rather than static data) and you need to search for numbers.
- By clicking Enter, we will see that the Base Salary of Bates Haley is $81,662.
Oh no – something went wrong! Common VLOOKUP Mistakes
Your data is ready, you just wrote your formula, you press Enter, and Excel gives you one of its #hashtags instead of a proper result. This list of common errors can help you diagnose the problem when using a VLOOKUP!
#N/A – When using [range_lookup] with approximate (TRUE), the search parameter (lookup_value) might be smaller than the smallest value in the first column of our search area (table_array). Typos or additional spaces in the lookup data are common mistakes that may cause this error.
#REF! – This error typically occurs when the index of the data we are looking for (col_index_num) is greater than the number of columns in our table. Another reason might be missing references in the VLOOKUP formula, such as removing the data table.
#VALUE! – If you try entering a value less than 1 for the search area (table_array), you get this error.
#NAME? – Typically, you get this error if you are missing any quotation marks in the formula. For example, if you’re specifically looking for “Bates Haley”, you should enter
=VLOOKUP(“Bates Haley”,B2:E7,2,FALSE)
That wasn’t so hard!
Searching through vast amount of data in Excel can be extremely tedious if you don’t utilize certain formulas. VLOOKUP allows you to identify and index information extremely easy and, contrary to popular belief, is not hard to implement. Once you understand the logic behind generating the formula’s syntax, we are confident that you will use it at every opportunity. Follow us for more tutorials!
