How to Use the VLOOKUP Function in Excel 2021

Edited 3 weeks ago by ExtremeHow Editorial Team

Microsoft OfficeExcelVLOOKUPFormulasWindowsMacAdvancedProductivity

The VLOOKUP function in Excel is one of the most powerful tools for data management and analysis. It allows users to find and retrieve data from a specific column within a table based on a matching value in the first column. This function is extremely useful in a variety of scenarios, from simple tasks like looking up a product price in a catalog to more complex data analysis scenarios.

Understanding VLOOKUP

Before we get into the specific steps of using the VLOOKUP function, it's essential to understand what it does and how it works. VLOOKUP stands for "vertical lookup." This means that it searches for a value vertically down through the first column of a table. Once a matching value is found, VLOOKUP retrieves the value from the specified column in the same row.

The VLOOKUP function has four parameters:

1. lookup_value: This is the value you want to find in the first column of your data table. This is what VLOOKUP looks for.
2. table_array: This parameter defines the range of cells through which the function should search to find your lookup value. It is always important to ensure that the lookup value is in the first column of this range.
3. col_index_num: Once a match is found for the lookup value, this parameter tells Excel which column number from the table array to return the value from. Note that this is not the Excel column letter, but the number of the corresponding column from the table array.
4. range_lookup: This is an optional parameter that determines whether you want an exact or approximate match. If you set it to TRUE or leave it blank, Excel will look for an approximate match. If you set it to FALSE, Excel will look for an exact match.

Syntax of VLOOKUP

In Excel, the syntax for writing the VLOOKUP function is as follows:

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

As we explore how to use VLOOKUP, let's understand each of these components in more detail with examples.

VLOOKUP usage example

Let's consider an example where you have a table of employee data containing their IDs, names, departments, and salaries. Let's assume this data is organized in columns A to D, with each row representing a different employee.

Step 1: Prepare your data table

You have the following information in cells A1:D6:

ID Name Department Salary
101 Alice HR 50000
102 Bob Sales 60000
103 Charlie IT 70000
104 David Marketing 55000
105 Eve HR 65000

Step 2: Using the VLOOKUP function

Suppose you want to know which department Charlie works in. You would use the VLOOKUP function to find the data.

=VLOOKUP("Charlie", A2:D6, 3, FALSE)

This VLOOKUP function works as follows:

• lookup_value is "Charlie". This is the value you are looking for in the first column of your table array.
• table_array is A2:D6. This range contains all the data in the table.
• col_index_num is 3, which means you want to return data from the third column (department). Remember that column numbers are counted from the leftmost column of your table array.
• range_lookup is set to FALSE because you want an exact match.

This function will return "IT", which is the department where Charlie works.

Key points to remember

• Make sure that your lookup value exists in the first column of the specified table array; otherwise, Excel won’t be able to find it.
• The column index number must be between 1 and the total number of columns in your table array.
• If you set range_lookup parameter to TRUE or leave it blank, make sure that your data is sorted in ascending order to get accurate results.

Handling errors with VLOOKUP

When using the VLOOKUP function, you may encounter some common errors, such as:

• #N/A – This error occurs when the function cannot find the lookup value. Check if the value exists in the first column of your table array.
• #REF! – This appears when col_index_num is greater than the number of columns in table_array.
• #VALUE! – This can occur if one or more of the arguments provided to VLOOKUP have the wrong data type.

Excel provides ways to handle these errors gracefully, such as wrapping the VLOOKUP function with an IFERROR statement:

=IFERROR(VLOOKUP("Charlie", A2:D6, 3, FALSE), "Not Found")

If Charlie's information cannot be found, this formula will return "Not Found" instead of an error.

Tips for using VLOOKUP effectively

• Use absolute references: Use absolute references (using the $ symbol) for your table array to keep the range fixed when you want to copy your VLOOKUP formula across cells.
• Verify data consistency: Make sure your data is consistent and has no leading or trailing spaces, as this can cause unexpected errors in matching values.
• Consider INDEX and MATCH: For greater flexibility and where VLOOKUP has limitations (such as searching to the left), consider using a combination of INDEX and MATCH functions.

Conclusion

The VLOOKUP function is a robust tool in Excel 2021, enabling users to efficiently view and manage data in their spreadsheets. Understanding its syntax and how each parameter works makes it an invaluable asset for data analysis. By mastering VLOOKUP, Excel users can easily navigate through huge datasets, accelerating decision-making and insight retrieval.

Remember to practice with different datasets and try to combine VLOOKUP with other Excel functions to take advantage of its full potential. With time and patience, you will find it easier to perform data lookups and create dynamic reports in a variety of work situations.

  Share This Article

If you find anything wrong with the article content, you can request an update