In this blog post, we’re going to take a look at how to use the **VLOOKUP** function. The **VLOOKUP** function is one of the most commonly used and asked about Excel functions.

The **VLOOKUP** function enables you to perform a very useful method of finding data in a worksheet known as a lookup. A lookup can be extremely useful when working with large amounts of data and you want to dynamically find a specific piece of information based on a value. The **VLOOKUP** function is probably the simplest of all methods of carrying out a lookup.

The **VLOOKUP** function finds the row where a specific value is in the leftmost column in a range of cells. The **VLOOKUP** function then returns a value from a cell on the same row but in a different column. You can specify the column from which to return the value.

If you want to download the example spreadsheet used in this blog post and a PDF guide to the **VLOOKUP** function, click the download button below.

# Quick Example

The goal of the **VLOOKUP** function is to search a range of cells known as a **table_array** for a value known as the **lookup_value**. Once the **VLOOKUP** function finds the **lookup_value**, it returns a value in a corresponding number of columns to the right that is specified by the **col_index_num** parameter.

This principle is represented in the image below.

The **V** in the term **VLOOKUP** stands for “vertical.” The reason why vertical is important is that the **VLOOKUP** function searches for the **lookup_value** vertically in the leftmost column of a range of cells, known as the **table_array**, starting from the top and working downward.

Let’s take a quick look at an example. Imagine we have a worksheet containing employee data. In the first column is the employee’s name, and each column to the right of the employee’s name contains details specific to the employee. We’re going to use the **VLOOKUP** function to search for an employee called **Delilah** and get the **VLOOKUP** function to return her position title using the following formula.

**=VLOOKUP (F3, A1:D21, 3, 0)**

We simply insert this formula in cell **G2**.

The formula returns the value **Development Manager** as **Delilah’s** position, which is correct.

# Syntax

The syntax of the **VLOOKUP** function is **VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).**

Parameter | Description |
---|---|

lookup_value | This is the value that the VLOOKUP function searches for in the leftmost column of the table. |

table_array | This is the range of cells that the VLOOKUP function uses. The first column of the selected range of cells will be the one in which the lookup_value will be looked up. |

col_index_num | This is the number of the column to the right of the leftmost column that you want to extract the data from. The numbering starts at 1, being the leftmost column. |

range_lookup | This is an optional logical value. The default value is 1 (TRUE). This means that it will search for the closest numerical or alphabetical value to your lookup_value if it can`t find your exact search. The 0 value (FALSE) will specifically search for your lookup_value, and it will provide the #N/A value if it can`t find it. |

# Example

Returning to our earlier example where we searched for **Delilah**, we used the **VLOOKUP** function to find the position of a specific employee in a range of cells, using the employee’s name as the **lookup_value**.

The formula used in this example was:

**=VLOOKUP (F3, A1:D21, 3, 0)**

Let us break this formula down:

**lookup_value**– the**lookup_value**is**F3**, which is a reference to a cell where a user inputs a**lookup_value**. In our example, we are looking for**Delilah**. You can also replace the cell reference to F3 with the actual name of the employee. This would change the formula to look like**=VLOOKUP (“Delilah”, A1:D21, 3, 0)**. The result would be the same.**table_array**– the**table_array**parameter is**A1:D21**, and it represents the range of cells to be used by the VLOOKUP function. Our table_array is the range of cell**A1**to**D21**.**col_index_num**– the**col_index_num**parameter is**3**. This specifies the number of columns to the right in the**table_array**that we will return our value from. Column**C**is the third column of our**table_array**.**range_lookup**– the**range_lookup**parameter value is**0**. This means that the**VLOOKUP**function will carry out an exact match for the**lookup_value****Delilah**. If it doesn’t find her within the**table_array**, it will display the**#N/A**

# Case-Sensitivity

The **VLOOKUP** function on its own isn’t case-sensitive. That means it won’t differentiate between **DELILAH**, **Delilah** and **delilah**. It will match them exactly the same.

If you have a worksheet that contains the same values but a different case in the lookup column, it will match the first value it finds. So for instance, if you have **Delilah**, **Delilah** and **delilah** in the leftmost column of your **table_array**, it will match the first value it finds as shown in the image below.

# Range Lookup Values

The **VLOOKUP** function **range_lookup** parameter can take two different values. The value can be number **0** or the word **FALSE**. Alternatively, it can be** 1** or the word **TRUE**. If no **range_lookup** parameter value is supplied, the **VLOOKUP** function defaults to the value **1** (**TRUE)**.

If the value is set to **0** (**FALSE)**, the **VLOOKUP** function will carry out an exact match against the **lookup_value**. However, if the **range_lookup** parameter value is **1** (**TRUE)**, the **VLOOKUP** function will carry out an approximate match.

## Exact Match

With the exception of case sensitivity, when carrying out an exact match, the **VLOOKUP** function will only return a value if it finds an exact match to the **lookup_value** in the leftmost column of the **table_array**.

In our previous example, we used the following formula:

**=VLOOKUP(F2,A1:D21,3,0)**

Notice that the last value, the **lookup_value**, is **0**. Previously, we have searched for **Delilah**. However, we could have searched for **Ken** as shown in the image below.

If an exact match isn’t found, then the **VLOOKUP** function will return the **#N/A** error as shown in the image below.

## Approximate Match

**VLOOKUP** uses approximate match by default. When using an approximate match, it is best to have the leftmost column sorted either alphabetically or numerically. If the leftmost column isn’t sorted in this manner, the **VLOOKUP** function may return unexpected values.

For example, if we take our previous example and change the **range_lookup** value to **1**, the formula now looks like this:

**=VLOOKUP(F2,$A$1:$D$21,3,1)**

If then sort our table in reverse alphabetical order (**Z-A**), using the following button on the **Data** tab of the Ribbon.

The Windows and Mac menus look slightly different.

The formula now returns the **#N/A** error even though our **lookup_value Ken** exists in our **table_array**. As shown below.

The approximate match will return a value in the following scenario. First, let’s sort our data alphabetically (**A-Z**). Use the following button on the **Data** tab of the ribbon.

Now type **Kev, **not** Ken** in cell **F2**. This name doesn’t exist in our **table_array**.

This time, the formula returns the value **Excel Master**. This is because the name **Kev** is an approximate match to the name **Ken**, which is in our **table_array**.

Using approximate matching can be unpredictable. I strongly suggest using the exact match value 0 for the **range_lookup** parameter unless you have a very specific reason not to.

# Preparing Your Data

If designing your spreadsheet from scratch, it is worth spending five minutes thinking about the data you intend to collect, use, and/or analyse. For the **VLOOKUP** function, there are a couple of golden rules to think about.

- The leftmost column must contain your
**lookup_value**. Try to keep these values unique and have a value for every row.

- Keep all data that relates to the
**lookup_value**on the same row. Use different columns for each piece of data that relates to the**lookup_value**. Make sure you use the same column on each row for the same types of data, i.e., a column for telephone numbers and another column for job titles.

# Reference Types

If you intend to copy and paste a formula containing the **VLOOKUP** function, **absolute references** can save you a lot of time and editing. For instance, in our example, if we want to return more data relating to a specific employee, such as the employee’s salary, we could copy and paste the same formula into cell **H2** and add a new heading, **Salary**, to cell **H1** like in the image below:

The formula now looks like this:

**=VLOOKUP(G2,B1:E21,3,0)**

However, this formula now references the range of cells **B1:E21** in the **table_array** parameter and the cell **G2** for the **lookup_value** parameter. The formula returns a **#N/A** error because it is now looking for the **lookup_value** in column **B**, which doesn’t contain the employees’ names.

This is because we have pasted the formula one column to the right, and Excel updated the cell references so that they now reference cells one column to the right.

To get around this, we can use **absolute cell** references. To create **absolute cell** references, we place a dollar sign (**$**) in front of either or both the **column** and **row** reference. Going back to our original formula, we can make our cell references absolute as follows:

**=VLOOKUP($F$2, $A$1:$D$21,3,0)**

We can now copy and paste this formula in to cell **H2**. When we finish, the worksheet will look like the image below.

It is now returning the **Position** value for **Ken** not his **Salary** in cell **H2**. We need to update the **col_index_num** parameter value to **4** so that it returns the salary information. Once we’ve done this, the formula should now look like this:

**=VLOOKUP($F$2,$A$1:$D$21,4,0)**

Our worksheet now returns the correct salary information.

For more information on Absolute references, check out my post by clicking here.

# Named Ranges

Using **named ranges** with the **VLOOKUP** function, like most other functions, makes the **VLOOKUP** function easier to read and understand**. Named ranges** also make spreadsheet maintenance easier. Using a **named range** for your **table_array** parameters enables you to add rows to the **table_array** without needing to update your formula containing the **VLOOKUP **function.

To do this, we need to create **named ranges** for the employee data. Start by selecting the cells **A1:D21** and type **EmployeeData** in the **name** **box** and press enter as shown in the image below.

Now, we can use the named range **EmployeeData** in the formula as follows:

**=VLOOKUP(F2,EmployeeData,3,0)**

The formula is now much easier to read, plus you can change the size of the **table_array** without touching the formula. Once this is done, we can insert the formula into cell **G2**.

For more information on named ranges, check my post by clicking here.

# Wildcard Characters

When carrying out an **exact match**, you can use wildcard characters in the **lookup_value **parameter. For instance, you may want to search a list of employee names that start with the text **Ric**. To do this, we would use the following formula:

**=VLOOKUP(F2&”*”,$A$1:$D$21,3,0)**

Note that we concatenate the value input into cell **F2** with the wildcard character, asterisk (*****), using the expression **F2&”*”**. This will match the first value found that starts with the value input into cell **F2**, irrespective of the text that follows. When we insert the formula into our worksheet, it returns the value **Analyst. **This is because the name **Richard, **from our **table_array, **matches our lookup_value **Ric*.**

For more information on wildcard characters, check my post by clicking here.

# Common Errors

The **VLOOKUP** function can return several different errors.

## #N/A Error

When carrying out an exact match, (**range_lookup** value **0** or **FALSE)**, the **VLOOKUP** function returns the **#N/A** error when the **lookup_value** can’t be found.

If carrying out an approximate match, (**range_lookup** value **1**, or **TRUE),** the **VLOOKUP** function returns the **#N/A error** when the **lookup_value** is smaller than the smallest value in the left most column of the **table_array.** This might sound a little weird when the **lookup_value** is a text string, but all values in Excel have a numerical equivalent.

If we carry out an exact match using our example, and we input the value **David** into cell **F2**. It returns the **#N/A** error.

This is because our **table_array** doesn’t contain the name **David** in the first column.

You can trap **#N/A** errors and display a friendly message using the **IFNA** function.

We can modify our formula to include the **IFNA** function as follows:

**=IFNA(VLOOKUP(F2,$A$1:$D$21,3,0),”No Match”)**

When the **VLOOKUP** function returns the **#N/A** error, the **IFNA** function replaces the error message with the text “**No Match**,” as shown below.

This text is a little more user-friendly. For more information on the **IFNA** function, click here.

## #REF! Error

The **#REF!** Error is likely to occur if the **col_index_num** isn’t a valid value and is higher than the number of columns in the **table_array**.

For instance, if we modify our formula as follows:

**=VLOOKUP(F2,$A$1:$D$21,5,0)**

The formula is now referenced s **col_index_num 5**. Our **table_array** only has **4** columns, which results in the **#REF!** error.

## #NAME? Error

The final error we will discuss in relation to the **VLOOKUP** function is the **#NAME?**. The main reason you might get the **#NAME?** error is a typo in the formula. The formula thinks you have referenced a name such as a **Named Range**, **Named Constant**, or another **function**.

For instance, if we type the following formula into our worksheet:

**=VLOOKUP(F,$A$1:$D$21,3,0)**

This formula will return the **#NAME?** error.

This is because Excel thinks we have referenced a named name of some kind called **F**, when in fact, we wanted to reference cell **F2** but forgot to type the number **2 **after the letter **F**. Typed correctly, our formula looks like this:

**=VLOOKUP(F2, $A$1:$D$21,3,0)**

This formula now works just fine as shown on the next page.

# Limitations of the VLOOKUP Function.

One of the key problems with the **VLOOKUP** function is that you can only look up values to the right of the first column in the **table_array**. However, what If the employee’s names, the **lookup_value,** was in a column that is to the right of the values we wanted to return. For instance, in the image on the next page, we wouldn’t be able to return the employee’s age.

This is where **INDEX MATCH** comes in. **INDEX MATCH** can achieve everything **VLOOKUP** can, but is a little more complicated to learn. Click here to read my post on how to use **INDEX MATCH**.

# Wrap-up

That’s all for this blog post. I hope you’ve found it useful. As you can see, the **VLOOKUP** function can be useful. You’ve learned the difference between exact and approximate matching. You’ve seen how to use the **VLOOKUP** function with **named ranges**, **absolute** and **relative references**, and **wildcard characters**; and you have seen some of the errors that can occur.

Despite some of the **VLOOKUP** function shortcomings, it is still an extremely popular and useful function.

For tools and resources to help you learn Excel, check out my resources page.