What Is the Lookup Function?

VLOOKUP is a spreadsheet function designed to search for a value in the first column of a selected table and return information from another column in the same row.

The letter “V” refers to vertical lookup because the search is performed vertically through the first column of the selected range.

For example, imagine that you have a product list containing product codes, names, prices, and categories. Instead of manually searching through hundreds of rows, you can use a formula to find the product code and automatically return the corresponding price.

VLOOKUP Syntax Explained

The basic syntax is:

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

Each argument has a specific purpose:

Exact Match

For most searches involving product codes, employee IDs, customer numbers, or other unique identifiers, an exact match is usually appropriate.

=VLOOKUP(A2,$E$2:$H$100,3,FALSE)

The FALSE argument tells Excel to look for an exact match.

Approximate Match

An approximate match can be useful for ranges such as grades, commissions, tax brackets, or pricing levels.

=VLOOKUP(A2,$E$2:$F$10,2,TRUE)

When using approximate matching, the first column of the lookup table generally needs to be sorted in ascending order.

5 Practical Examples

Example 1: Finding a Product Price

Suppose you have a table containing product codes and prices. You can search for a code stored in cell A2 and return the price from the third column.

=VLOOKUP(A2,$E$2:$G$100,3,FALSE)

Excel searches for the value in A2 within the first column of the selected range and returns the corresponding value from column 3.

Example 2: Finding an Employee Department

Imagine a worksheet containing employee IDs, names, departments, and job positions.

=VLOOKUP(A2,$E$2:$H$100,3,FALSE)

This formula can return the department associated with the employee ID.

Example 3: Returning a Customer Name

If a customer ID is entered into a cell, you can retrieve the corresponding name from a database.

=VLOOKUP(A2,$E$2:$G$500,2,FALSE)

This can be useful when working with customer lists, sales reports, and order databases.

Example 4: Calculating a Commission Level

Approximate matching can be useful when a result depends on a numerical range.

=VLOOKUP(B2,$E$2:$F$10,2,TRUE)

In this example, Excel compares the value in B2 against the ranges in the first column and returns the corresponding level.

Example 5: Combining the Function with IFERROR

If the searched value does not exist, Excel may return a #N/A error. You can use IFERROR to display a more understandable message.

=IFERROR(VLOOKUP(A2,$E$2:$G$100,3,FALSE),"Not Found")

This makes spreadsheets easier to understand and prevents error messages from appearing in reports.

Common Lookup Errors and Fixes

#N/A Error

The #N/A error usually means that Excel could not find the searched value.

Check whether the value exists in the first column of the selected range and make sure there are no unnecessary spaces or differences in the data.

#REF! Error

The #REF! error can occur when the column number specified in the formula is outside the selected table.

For example, if the selected range contains only three columns, using column number 4 will cause a reference error.

#VALUE! Error

The #VALUE! error can occur when one of the arguments in the formula is incorrect or contains an invalid value.

Incorrect Results

If the formula returns an unexpected result, check the fourth argument. Using TRUE instead of FALSE can produce unexpected matches when the data is not properly sorted.

VLOOKUP vs XLOOKUP vs INDEX/MATCH

Excel offers several ways to search and retrieve information. The best option depends on the version of Excel you use and the structure of your data.

XLOOKUP is available in newer versions of Excel and can perform searches in directions that traditional VLOOKUP cannot. INDEX and MATCH can also provide flexible solutions when the data structure requires more control.

❓ Frequently Asked Questions

What is VLOOKUP used for?

It is used to search for a value in a table and return related information from another column in the same row.

What does FALSE mean in the formula?

FALSE tells Excel to search for an exact match.

What does TRUE mean?

TRUE enables an approximate match. It is commonly used with numerical ranges and requires appropriate ordering of the lookup column.

Why am I getting #N/A?

The searched value may not exist in the first column of the selected range, or the data may contain differences such as extra spaces or inconsistent formatting.

Can VLOOKUP search to the left?

Traditional VLOOKUP is designed to return information from columns to the right of the lookup column. For more flexible searches, XLOOKUP or INDEX/MATCH can be alternatives.

Can I use VLOOKUP with another function?

Yes. It can be combined with functions such as IFERROR, IF, MATCH, and other Excel functions to create more advanced formulas.

Conclusion

VLOOKUP in Excel is a useful tool for searching tables and retrieving related information automatically. Once you understand its arguments and how exact and approximate matching work, you can use it in many different spreadsheet scenarios.

Start with simple tables and exact matches, then practice with larger datasets and combinations with other functions. As you become more comfortable, explore XLOOKUP and INDEX/MATCH to expand your Excel skills.