VLOOKUP Excel Function

If you’ve ever spent precious time manually searching through a sea of data to find a particular value, VLOOKUP could save you a ton of time.

With VLOOKUP as your ally, you can unlock new possibilities in data analysis and save valuable time.

In this comprehensive guide,we’ll cover everything you need to know about the VLOOKUP Excel function. Read on to learn more.

What is VLOOKUP in Excel?

VLOOKUP is a function in Microsoft Excel and most other spreadsheet programs, like Google Sheets, that allows you to search for a specific value in a vertical column (known as the lookup table) and retrieve a corresponding value from a different column within the same row.

The term “VLOOKUP” stands for “Vertical Lookup.”

VLOOKUP is a versatile tool that allows you to search for specific values in a table and retrieve related information from another column. It has a wide range of applications, from finding data in large datasets to merging information from multiple tables. Understanding how to use VLOOKUP effectively can save you time, streamline your workflow, and provide valuable insights from your data.

Please note that VLOOKUP is still supported in Excel, but they are pushing for you to learn XLOOKUP instead, which superceded the VLOOKUP and HLOOKUP functions in Excel.

VLOOKUP Excel Formula Syntax

The syntax for the VLOOKUP function is as follows:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value you want to search for in the first column of the lookup table.

table_array: The range of cells that contains the lookup table, including both the lookup column and the column(s) from which you want to retrieve the value(s).

col_index_num: The column number within the lookup table from which you want to retrieve the result. It represents the offset from the leftmost column in the lookup table.

range_lookup: (optional) A logical value that specifies whether you want an exact match or an approximate match. If set to TRUE or omitted, it assumes an approximate match. If set to FALSE or 0, it looks for an exact match.

How to Use VLOOKUP in Excel

In this section, we’ll be looking at a couple of different ways we can use VLOOKUP in Excel using examples. Below is the spreadsheet we’ll be using:

Example data

How to Do VLOOKUP in Excel

VLOOKUP can help us find the exact match in a column.  For example, in our example worksheet, we can find the student with the ID number 2947.

Example sheet

Here’s how to VLOOKUP in Excel:

  1. In the cell you want, type =VLOOKUP().
In the cell you want, type =VLOOKUP().
  1. After the opening brackets, select the cell with the search value and add a comma,
After the opening brackets, select the cell with the search value and add a comma
  1. Select the range of data you want to search and a comma:
Select the range of data you want to search and a comma
  1. Enter the column index number. This is the column where you think the answers are, and it must be to the right of your lookup values:
Enter the column index number
  1. Enter the range lookup value, and FALSE finds exact matches. Your finished formula looks something like this:
=VLOOKUP(A13,A:2:D:10,2,FALSE)
Enter the range lookup value, and FALSE finds exact matches.

This formula will return the correct name corresponding to ID 2947, which is Tony Jackson.

How to Use VLOOKUP to Do Two-way Lookup

The VLOOKUP can usually return a corresponding match from a column only. However, we can combine the VLOOKUP function with the MATCH function to determine the column index number of each field.

This way, we can perform a 2-dimensional lookup involving both row and column. For this example we’ll be using the example sheet below:

Example sheet

Here’s how to do a 2-way lookup with VLOOKUP in Excel:

  1. In the cell you want, type =VLOOKUP().
In the cell you want, type =VLOOKUP().
  1. After the opening brackets, select the cell with the search value and add a comma.
elect the cell with the search value and add a comma
  1. Select the range of data you want to search and a comma.
Select the range of data you want to search and a comma.
  1. Enter the MATCH formula:
Enter the match formula:
  1. Select the header row as the search value.
Select the header row as the search value.
  1. Select the row and add a 0 for the exact match.
Select the row and add a 0 for exact match.
  1. Close the brackets and another 0 for an exact match.
Close the brackets and another 0 for an exact match
  1. Enter the range lookup value, and FALSE finds exact matches. Your finished formula looks something like this:
=VLOOKUP(A13,A:2:D:10,2,FALSE)
Two way lookup results

This makes your formula more dynamic.

How to Use VLOOKUP with Multiple Criteria

Suppose we wanted to look up more than one column of information. For example, in our example worksheet below, we can find the Marks for Melissa Danielle, specifically in Biology.

Example sheet

To do this, first, we’ll need to create a helper column that combines the criteria.

  1. Right-click on a column and choose Insert > Entire column.
Right click and select insert
  1. Write the header Helper column.
  2. In the cell type the formula = A2&” “&B2
Joining formula in the helper column
  1. Drag the formula to the rest of the cells.
Drag the formula to the rest of the cells.
  1. In the cell you want to return the results, type the VLOOKUP.
  2.  Combine the headers with the formula H2&” “&I1
Combine the headers with the formula H2&” “&I1
  1. Select the range from the helper column and a comma.
Select the range from the helper column and a comma.
  1. Add the column index.
  2. Add a 0 or FALSE for an exact match.
Add a 0 or FALSE for an exact match.
  1. Click Enter.
Results of Vlookup with multiple criteria

With this formula, we can use more than one condition for the lookup. In our example, we looked up both the names and the subjects to return the marks.

How to Use VLOOKUP with Wildcards

Wildcards in Excel are special characters that represent unknown or variable values in text-based functions and formulas. They allow you to perform flexible searches, matches, and replacements within your data.

Unlike Google Sheets, Excel only supports two main types of wildcards: the asterisk (*) and the question mark (?).

Asterisk (*) wildcard- It can be used at the beginning, middle, or end of a text string to represent a value.

Question mark (?) wildcard- It can be used to find a specific character in a specific position within a text string to represent a single character.

Here’s how to use VLOOKUP with Wildcards:

  1. In the cell you want, type =VLOOKUP().
  2. After the opening brackets, write the text and add the wildcard.
After the opening brackets, write the text and add the wildcard.
  1. Select the range of data you want to search and a comma:
Select the range of data you want to search and a comma:
  1. Enter the column index number.
Enter the column index number.
  1. Enter the range lookup value, and FALSE finds exact matches. Your finished formula looks something like this:
=VLOOKUP(“Melissa*”,A:2:D:10,4,FALSE)
Enter the range lookup value, and FALSE finds exact matches.

This will return the first match for the column corresponding to any cell with the name Melissa in it.

These are just a few examples of how you can use VLOOKUP in Excel. You can even use VLOOKUP from another sheet.

Tips to Use VLOOKUP Function Efficiently

The VLOOKUP function is a very powerful function, but only when used right. Here are some tips for using the VLOOKUP function efficiently in Excel:

  • To ensure accurate results, sort your lookup table in ascending order based on the column you are performing the lookup on. VLOOKUP requires the data to be sorted in order to function properly when using an approximate match.
  • When using the VLOOKUP function, lock the lookup table range using absolute cell references (e.g., $A$1:$B$10). This will prevent the range from changing when you copy the formula to other cells, ensuring consistent lookup ranges.
  • Consider assigning a named range to the lookup table. This makes it easier to refer to the table in the VLOOKUP formula, improves formula readability, and reduces the chance of errors.
  • VLOOKUP may return #N/A if the lookup value is not found. To handle this, you can use the IFERROR function to display a custom message or perform an alternative action when no match is found.
  • The range_lookup argument determines whether VLOOKUP should perform an approximate match (TRUE) or an exact match (FALSE). Understand the implications of each option and choose the appropriate one based on your data and requirements.
  • VLOOKUP can be combined with other Excel functions like IF, INDEX, MATCH, or nested within other formulas. Exploring these combinations can enhance the flexibility and power of your calculations.
  • Always test your VLOOKUP formulas with different lookup values to ensure they return the expected results. Double-check the accuracy of retrieved values against the original data.
  • If you’re using VLOOKUP in complex spreadsheets, consider documenting your formulas to provide clarity and facilitate future understanding or troubleshooting.

Common Errors in VLOOKUP Function

When using the VLOOKUP function in Excel, there are several common errors that you may encounter. Here are some of the most frequent errors and their potential causes:

#N/A

This error occurs when the VLOOKUP function cannot find an exact match for the lookup value in the first column of the lookup table. It can happen due to misspellings, inconsistent formatting, or differences in the data between the lookup value and the lookup table.

For example, in the table below, we spelled Melissa Danielle incorrectly.

Example of #N/A error

#REF!

This error occurs when the range specified in the table_array argument is incorrect or has been deleted. It can happen if you delete a column or row that is part of the lookup table, causing the range reference to become invalid.

It can also happen if you copy the formula from another sheet or if you use a column index that is not within the selected range.

For example, in the table below, the reference does not exist.

Example of #REF error

#VALUE!

This error occurs when there is an issue with the data types of the lookup value or the lookup table. It can happen if the data types are incompatible or if there are non-numeric characters in a column that should contain only numbers.

#NAME?

This error occurs when Excel cannot recognize the function name or when there is a typo in the formula. It can happen if you misspell the function name (e.g., “VLOOKUP” instead of “VLOOKUP”) or if you accidentally delete part of the formula.

Example of #NAME? error

Wrapping Up

This article aims to serve as your comprehensive guide to harnessing the power of the VLOOKUP  Excel function, unlocking its potential to streamline your data analysis processes.

With the few examples above, you should be able to navigate around your worksheet with the VLOOKUP function.

You can also check out our premium templates below. Remember to use the code SSP at checkout to save 50% on any purchases!

Get Premium Templates

Related:

Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!