In its simplest form, VLOOKUP is used to return a value from a table of data based on a lookup value you provide.
The function also allows for an exact match or a near match (if an exact match is not found).
NOTE: For the function to work as expected, the lookup table should be ordered by the index column (specifically with ‘approximate’ search), and the index values should avoid duplicate values.
Function Syntax
=VLOOKUP(lookup_value,table_array,col_index_num[,range_lookup])
Usage
More simply:
=VLOOKUP("What we want to look up?"
, "Where we want to lookup?"
, "Which column we want the result of the lookup from?"
, "Do we want only an Exact match for our lookup value, or to allow for a nearest match alternative?")
User example:
A typical user question leading to using the VLOOKUP function might be:
"I want to look up the country "USA" in the table of "Country Facts" and return the 'population size' which is in the 6th column".
In this example the table_array is the data in the Country Facts table we will be referring to. The column containing the country names is the index column – which for VLOOKUP it has to be the first column in the table_array. ‘6’ with be the col_index_num, being the number of columns the ‘population size’ column is to the right of the index column.
‘USA’ is the lookup_value. Note that we won’t want an approximate match on ‘USA’, if its missing or labelled differently (eg. ‘United States of America’), otherwise we might end up with the population of, say, the ‘United Kingdom’ which might be approximately correct in our table but otherwise a nonsensical answer to our question.
In which case we’ll need to state that we want an exact match explicitly, using range_lookup, and if ‘USA’ can’t be found in the data we’d prefer to see an error – which we can deal with somehow – rather than wrong data.
We will see a use case for approximate match search later.
| Function argument | Argument meaning |
|---|---|
| lookup_value | This is a value you wish to lookup in the table_array or range you specify. The value can be static (eg. a number, date or text) or a reference to another cell’s content. |
| table_array | This is a range of cells containing the data you wish the lookup_value to refer to, and from where the return value will come. Note that the lookup_value will will reference the first column only, and that the result will need to be in any other column (including the first column), to the right. |
| col_index_num | This is the column number in the table_array where the result will be found. Note that a column number greater than the columns in the table_array will cause an error to be raised. |
| range_lookup | You can specify if there must be an exact match to the lookup_value or approximate match. Exact Match – use ‘FALSE‘ or ‘0‘ A return value will only be given if an exact match to the lookup_value is found in the first column of the table_array. Approximate Match – use ‘TRUE‘ or ‘1‘ or omit this argument. For this to work the table_array must be sorted numerically or alphabetically, on its first column. The function will try and find an exact match, but will return the value in the row closest to the search value otherwise. |
Examples
Return data based on an exact match
We are presented with a table of Invoice IDs (‘InvoiceID’), each Invoice row contained a Customer Number (‘CustomerNo’), a Description of the invoiced item and the Invoice Cost (‘Cost’). Each Row contains a unique Invoice No.
We want to create a lookup so that when someone enters an InvoiceID (in cell B8), they will get the invoice Cost back (in cell D8).
Firstly we will only want an exact match when we look for our InvoiceID. InvoiceID is column 1 and the Cost value we want returned is in column 4.
The data in our table (ignoring the headers) starts in A2 and finishes in D6. The header row is a set of labels and not part of the invoice data, so we wont want to reference it in our VLOOKUP function.
Putting things together things together
- lookup_value: this will be in cell B8
- table_array: our data is in the range A2:D6
- col_index_num: the lookup column in VLOOKUP is always column 1, the Cost column is 4 columns to the right, so the index number is 4
- range_lookup: we want an exact match, so the value required is FALSE (or 0)
=VLOOKUP(B8, A2:D6, 4, FALSE)
This gives the desire result, though we would probably want to format it later.
Using invalid data with VLOOKUP
What happens if we choose a lookup value which doesn’t exist? Lets try with an InvoiceID of ‘9999’.
Excel gives us a #N/A error – this is a specific error which Excel raises when a value cannot be found.
There are a few ways to handle invalid data. We can ignore it, validate the inputs (for example only allowing the user to select from a list of valid entries) or handle the exception.
Lets handle the exception with a helpful message to the user.
There are several options, and several error handling functions to choose from.
Because our error here is #N/A we will use the IFNA error handling function (introduced in 2013). We could have used ISERROR or IFERROR, but these will capture any error and we want specifically to capture the #N/A error here.
We will wrap our VLOOKUP function in the IFNA function, and return some helpful text for the user if an the InvoiceID isn’t found and a #N/A is raised by VLOOKUP.
Now when we lookup a invalid value, we get a more user friendly response.
TIP: If you just want to check if a value exists in the data’s lookup column, you can set the VLOOKUP’s col_index_num to ‘1’ and the result will return the input value if the data exists, otherwise an #N/A error indicates that the value was not found.
Issues and limitations with VLOOKUP
Ok, so we used InvoiceID as the lookup value, but what if we want to lookup the CustomerNo instead. Initially that’s not such as complex change. First we have to change the range table_array in the function so that the lookup_value references the first column, which will now have to be CustomerNo.
VLOOKUP only references columns to the right of the lookup value column
We meet the first limitation of VLOOKUP. Our data is in 4 columns, but we have to start our table_array on the lookup column, which is column B if we are looking up CustomerNo, and we can only reference columns to the right only. What would happen if the data we want to return was the InvoiceID – i.e. column ‘-1’ to the lookup value column? In short VLOOKUP won’t allow that.
TIP: If the index column you want to reference not to left of your data – you could either copy that column or move the required index column to be the first column in the data. VLOOKUP would then be able to access all other columns to the right of it.
With that in mind, our table_array becomes B2:D6.
VLOOKUP only references the first match it finds – not all matches
Also, because our Cost column is now only column 3 in the new table_array, we will have to update the col_index_num equal to 3.
Once we execute the function, we get an answer of 3500. But do you notice that there are in fact 2 entries for CustomerNo 137. One for $3500 (InvoiceID 1003) and one for $195 (InvoiceID 1006). We have a problem.
VLOOKUP alone is not the function we need here. VLOOKUP works great with unique index value datasets, but not for this example where the CustomerNo column does not contain unique values. So unless you need just the first value back (or you know your index values are unique) then you will need to use a different function for this query.
HINT: Considering other functions like SUMIF, or a pivot table as an alternative solution to the problem of multiple matches.
Making VLOOKUP formulas more dynamic
The original table_array example above used a static range. But what happens if the table is regularly updated with new rows added? Ideally you won’t want to update your VLOOKUP table_array range constantly. A great way to do this is with Excel Tables. We’ll take a quick look at how they might be used with VLOOKUP next.
TIP: When dealing with tables of data, Excel Tables are a great way to access tabular data via functions. They help enforce consistency and they are aware of their size. You can easily convert a tabular data set into a Excel table by selecting any cell within the data, by selecting the Table button from the Insert tab of the Ribbon, or directly with the shortcut CTRL + T.
The benefit of using the Table with your VLOOKUP function is as the table grows with new data, the function will continue to automatically reference the whole table. More on that it our Excel Table post.
Using VLOOKUP with Excel Tables
In the following example, the data table has been updated to be an Excel Table, which excel automatically named ‘Table1’ – though the table name can be changed manually afterwards if desired.
Using the MATCH function with VLOOKUP
We can go one step further and make the col_index_num (column number) dynamic too using the MATCH function. All we need to know is the column name we want to reference.
The MATCH function we construct will look for the word “Cost” in Table1’s headers row, and if found will return the column number, but only if it finds an exact match.
Tables use some unusual syntax, but they are fairly easy to find by selecting the relevant part of the table.
=MATCH("Cost",Table1[#Headers],0)This will return the column number in Table1 for the “Cost” heading.
=4We can now update the VLOOKUP function with both the table name for our table_array, and the MATCH function for the col_index_num (referencing the column name in cell C8). With that change, we can dynamically update both the InvoiceID (in cell B8), or change the column name (in cell C8) to choose which invoice data we want returned for any invoice.
=VLOOKUP(B8,Table1,MATCH(C8,Table1[#Headers],0),TRUE)
Resulting in the value we wanted originally
We can now change cell C8 to return the the ‘CustomerNo’, (i.e. col_index_num = 2) for the relevant InvoiceID. Make sure you spell the table header name correctly though.
Or just return the ‘Description’ column’s value (i.e. col_index_num = 3).
How to use VLOOKUP with an approximate match
VLOOKUP will use approximate search when the range_lookup argument is set to TRUE (or 1).
Setting the function’s approximate argument to TRUE is best when using a lookup with ranges of values – or value buckets.
A VLOOKUP example using the approximate match option
Lets consider the cost of postal delivery based on weight. Our table below indicates delivery costs increase at discrete weight intervals. The weight of any package falling between 2 weight intervals all incur the same cost. The cost, therefore, would be dependant on which ‘weight bucket’ our package falls into.
For example, in our case, any package weighing between 2Kg and up to (but not including) 3Kg would cost $3 to be delivered.
Let’s enter a weight of 3.5Kg, which is not a value in our table. The table is sorted with the weight column in ascending order. Therefore our 3.5Kg delivery falls in the range 3Kg up to (but not including) 4Kg, that is a $5 cost.
Note that a lower bound row of 0Kg (Zero) was included, which meant the minimum cost was always $1 for any delivery up to (but not including) 1Kg.
Similarly any weight equal to, or greater than, 6Kg will be a flat $15.
Related Topics
- Other Excel Functions
- Other Excel Reference Functions
F003