VLOOKUP() is a function that you use to get a specific item out of a set of data. Let’s say a customer calls and you want to pull up that customers info. With a short list it is easy to find a customer but imagine if your list had 200 customers, or 1000, would you go through each one one by one? No, we can use the VLOOKUP() function to get what we need. What the VLOOKUP does is get any data from a table based on a the value in the first column. Or as per definition in Excel: Looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.
The function accepts 4 parameters:
- lookup_value: this is the value in the first column we want to find
- table_array: the table we want to search
- col_index_num: the number of the column we want to get
- range_lookup: this allows you to choose an exact match (FALSE), or Approximate Match (TRUE)
- For most purposes just set this to FALSE – i.e. you want the exact match.
- However in some cases you are not looking for the exact match but an approximation, this is where this is set to TRUE (or ommited)
For the purpose of this article I will be using this Excel spreadsheet: Customer Table
Here is the basic data set:
So getting back to our original question: how do we get the customer info quickly, given the customer provides their name. So we’ll start with a simpler example. Let’s say the customer provides you with an his/her customer id. We can set up a form that will allow us to enter the id and get the customer’s details from the customer table:
Here is where the function comes in. If we use the data in tab Customer Data we can lookup the customer id (since it is the first column), from the the table in that tab, and get the relevant column (e.g. First_Name is column 2):
So this will take the value from D2 (which is where we will type in the Customer ID to search, look it up from the table A1:K27 in Search Form Tab, and return the value in column 2 that matches exactly (hence FALSE) to what we are searching for.
So for example if I type in C00102 the first name will get populated with Odon. All we need to do is copy the formula over to the other column and adjust the column number to be equivalent to what we want. With one small correction that we need to make D2 a relative reference since it will always be D2 which has the id. In addition we can make this a step easier. We can make a ‘hidden’ column in the search form to indicate the column number:
So now when we copy and paste the formula across we will maintain the reference to D2 while constantly changing which column to lookup:
Looking up multiple fields
Going back to our original problem, we wanted to find the customer based on First Name and Last Name. Of course we can make the problem simpler by having both names in one column. However in many cases your information is likely to come from a database where you will not have control over how the columns are. Luckily the solution to the problem of looking up multiple columns is actually the same as the simple solution. We will join together the first and last name and search on that. To do that, we first need to set up our data. We will need the first column to be a joining of the first and last name. We can simply insert a new column at the beginning. But remember doing so will also change nay references to the right (i.e. any vlookups you may have set up will not start from column a but will now start from column B). So if you get a regularly updated table you want to have a robust process for updating the information. Instead of inserting a new column every time, and the updating the VLOOKUPS we will create a template into which we will paste in any updated data.
In the template we will insert a new row in front of all the data and concatenate (join together) the first name and last name:
Fill down (Copy/Paste) this formula to all the customers. Next time you get an updated table, simple delete all rows except 1 and 2 (header and 1 row with data), paste in the updated info into column B and the copy and paste the formula down to the last customer. No references will need to be changed in the search form.
Next we will update the search form. We now need two input fields, one for First Name, one for Last Name. And we need to add the Customer_Id to the search result since we wont have this automatically now:
Now to the formula, we do the exact same thing as before, just now instead of looking up just D2, we will do the same join on the first name and last name as we did in the data:
Notice two points in the table we are looking for (‘Customer Data with Key’!$A:$L). I specifically did not define the rows, just the columns, this way no matter whether you have one row or thousands it will also look at all of them. Second of all we need to remember to freeze the columns themselves or they will also move as we go across.
So now we copy and paste the formula across hide the ‘helper’ row and save.
There is many cases where you might not have a unique field to search on. For example maybe you have a table with information by person per day. So each person has a separate entry per day. By joining together the day and the name of the person we can get a searchable unique field.
There is two variations on VLOOKUP(). The first one is the HLOOKUP(), which works exactly like VLOOKUP() but searches the first column and returns the value a specific row in that column. And the LOOKUP() which can behave just like exact match VLOOKUP() or can allow you to search a table on a field other than the first one.
An simple example how to use the hlookup is with a multiplication table:
If we used VLOOKUP() we can use field to define which value is in the rows and then another one for the column (since the column is simple the vlue in row 1 + 1. I.e. 2*4 would be from row 3 so will search for row 3, and 4 is in column E which is the 5th column across:
With HLOOKUP() we do exactly the same just in reverse:
However the VLOOKUP is usually the common since in most database structures it ill be the rows which will contain the unique key column(s) to search.
LOOKUP() can be used in a simple matter to get you a similar result to VLOOKUP
LOOKUP() will look for the value in the first column, and will return the value in the last column for the matching row (i.e. in this case value in E3 or 2*4=8
The more complex and useful form of the LOOKUP() allows you to specify exactly which column to search the value and which column contains the value to return:
Here we are using the power of the LOOKUP to look at the second column since any number multiplied by 1 is the same number. And then we are returning the value in column E i.e. 2*4.
While it may seem that this is less useful than the VLOOKUP(), it has advantages. If we look at the customer example. Once we added the key if we do a VLOOKUP() on the customer ID we must start from column B. Further to that we need to tell it which column to look for relative to the first column. With the LOOKUP() we tell it to always look at column B (i.e. the Customer_Id column) and the always return a specific column in he excel sheet. This way if we add or remove column in the data table, Excel will still maintain the reference to the right column, while a VLOOKUP() will still look at a specific column number even if the data changed. For example if we were to add a column after the customer id column in the original table, your search will still work but will return the wrong field.
For most of this article we look at an exact match option of VLOOKUP(), however sometimes we need to find the nearest match, not a specific match. For exmaple, say we have a list of people and their heights. And we want to find the person closest to a specific height (maybe you got a new product and marketing told you people of a specific height are more likely to use it). If we set up the data so that the first column is the height, and it is sorted by ascending height order then we can use and approximate match to find the person closest to the height. VLOOKUP’s approximate match option will try to return the exact match, but if it is not found will return the next biggest match that is smaller than the search value. E.g. if the height is 170cm then it will try to find someone who is 170cm. But if there is not such person, it will return the person who is closest to 170 and not over:
Note since the list actually had two people at 170, VLOOKUP() will return the last match found:
I used the following sites to generate the random data for this article: