How to use the VLOOKUP Function in Excel
The “Vlookup function” combined with the IF function would have to be some of the most used functions in all my Excel spreadsheets. The combination of these functions allows me to “look up” pieces of information from a list and perform some comparative analysis on my data. Very powerful stuff when you get the hang of it.
The Vlookup function does pretty much what its name suggests. It looks up a value in a vertical listing of data (hence the V in Vlookup) and returns information based on whether it finds the value or not.
To give an example of how you can use the Vlookup function, lets say you want to look up an employee’s phone extension using their first name. See the image of our example spreadsheet below. Cell A2 is our input box (where we type our employee names) and cell B2 shows the result of our Vlookup function (employee phone number in this case).
Vlookup Function example
Vlookup Function
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
2. table_array (required)
3. col_index_num (required)
3. range_lookup (optional)
To use the Vlookup function in your spreadsheets, you will need 4 pieces of information.
- lookup_value
- table_array
- col_index_num
- range_lookup
At first, this function may seem very intimidating as the information it requires and the terminology used are not that simple to understand. But like most things, once you actually understand what the terms are and the pieces of information it requires, it is really quite simple to use and may end up being one of your most favourite functions to use in Excel (it is one of ours !!).
Let’s look now at the pieces of information we need for this function:
1. Lookup_value – This is quite straight forward and is the actual value that you are using the search to look for data in your list. In our example spreadsheet above, the lookup_value will be our employees’ name. The value in cell A2 in our example.
2. Table_array – This is the list of data (or range) that we will look in to find our lookup_value. In our example, this is the range of cells from A5 to B20 that contains all our employees names and their phone numbers. This data can be on our existing worksheet, as in our example, or another worksheet within your workbook. When defining a table_array there are a couple of rules and caveats that you must remember – these are shown in the following highlighted box.
Table_array
There are a couple of rules and caveats to remember with using the table_array.
Rules & Caveats
3. Col_index_num – This is the number of the column in your list of lookup data (table_array) that has the information you are wanting to bring back. In our example, we want to find the employees phone number and that is located in column 2 of our table_array.
4. Range-lookup – This defines how close a match you need between the lookup_value (employee name) and the values in the leftmost column on our table_array. You enter either “TRUE” or “FALSE” for this component of the function.
Range_lookup
2. TRUE – we want the vlookup function to find the closest match it can to our lookup value
Ok, so to complete the formula in our example using the vlookup function. The formula that we enter into cell B2 is:
Download
You can download a PDF transcript of this tip by clicking the following link – How to use the vlookup function PDF download.
You can also download a copy of our example spreadsheet so you can explore this tip further – How to use the vlookup function – example spreadsheet.
Please Share
If you have found value from this article or know someone who may be able to benefit from it, please feel free to share it with your friends and spread the word on Facebook, Twitter or Linkedin.
