How to Use VLOOKUP in Excel 2016

Edited 4 months ago by ExtremeHow Editorial Team

Microsoft OfficeExcelVLOOKUPSpreadsheetFunctionsFormulasWindowsMacProductivityCalculation

In the world of spreadsheets, Microsoft Excel is one of the most powerful tools available to both individuals and businesses. It comes with a wide range of features designed to manage data, perform calculations, and display results in a user-friendly format. One of these features is the VLOOKUP function, which stands for "vertical lookup." This function is particularly useful for searching through data in columns and retrieving information from different parts of a spreadsheet.

Understanding the basics

Before knowing how VLOOKUP works, it is important to understand its main elements and requirements. The VLOOKUP function helps you find a particular value in the first column of a range and then returns the value in the same row from another column you specify. Here is a description of its syntax:

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

The meaning of each part is as follows:

• lookup_value: This is the value you are trying to find.
• table_array: The range of data where the lookup will be performed.
• col_index_num: The column number in table_array from which you want to get the value.
• range_lookup: This is an optional argument. Use TRUE for an approximate match or FALSE for an exact match (the default is TRUE).

When to use VLOOKUP

VLOOKUP is especially useful for large data sets where manual searching would be cumbersome. You can use it to look up prices, match names to their related information, bring together data from two different reports, or find any data that is arranged vertically in columns. For example, if you have a table of employee data with columns for ID, name, and department, you can use VLOOKUP to find out which department an employee belongs to by searching for his or her ID.

Set up your data

Before using VLOOKUP, it's important to make sure your data is properly structured. This function works best when:

• Your data is organized into columns with headings for clarity.
• The lookup value is in the first column of your selection range.
• You must ensure that the lookup column does not contain any duplicate values as this may lead to incorrect results.

Example of using VLOOKUP

Let's look at a simple example of how VLOOKUP works in practice. Suppose you have the following data in Excel:

    | A | B | C |
    | 1 | product id | product | price |
    | 2 | 101 | Apples | $1.20 |
    | 3 | 102 | Oranges | $0.80 |
    , 4 | 103 | Bananas $1.50 |

You want to know the price of a product using its Product ID. Let's say the Product ID you are looking for is 102.

To use VLOOKUP here, go to the new cell where you want the result to display and enter the following formula:

    =VLOOKUP(102, A2:C4, 3, False)

This will search for the product ID '102' in the first column of the range A2:C4. Once it finds a match, it will return the price from the third column of the matched row, which is $0.80 for oranges.

Explanation of range lookup

The range_lookup parameter determines whether VLOOKUP should search for an exact match or an approximate match. When using TRUE (approximate match), make sure the first column in your table is sorted in ascending order, as this will affect the accuracy of your results. Using FALSE is the more common approach because it ensures you get an exact match, which is generally more desirable when dealing with categorical data.

Common pitfalls in VLOOKUP

VLOOKUP is extremely useful, but some common mistakes can lead to errors or unexpected results:

• Case Sensitivity: VLOOKUP is not case-sensitive. Make sure you consider this when working with text values that differ only in case.
• Column index error: If you enter a column number that does not exist in the table array, you will probably get a #REF! error.
• Exact match: If you don't specify range_lookup as FALSE and the data isn't sorted, you might get incorrect results.
• Data types: Make sure the data types match; if one is a number and the other is text, VLOOKUP will not be able to find a match.

VLOOKUP with N/A errors

One of the most common errors when using VLOOKUP is the #N/A error, which indicates that the function could not find the lookup value in the specified range. Here are some strategies for handling this error:

• Double-check the lookup value for typing mistakes.
• Make sure you are searching in the correct column.
• Consider using the IFERROR function to catch #N/A errors and return a more user-friendly message. For example:

              =IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, False), "Not Found")
          

Using VLOOKUP in sheets

You are not limited to using VLOOKUP within a worksheet. You can also perform lookups on different sheets in the same workbook. When referencing another sheet, include an exclamation mark (!) followed by the sheet name before the cell range. For example, to reference cells A2 to C4 in a sheet named “Products,” use:

    =VLOOKUP(lookup_value, product!A2:C4, col_index_num, False)

Advantages of VLOOKUP

VLOOKUP is an important tool for those who deal with large data sets. It simplifies data analysis by making it easy to cross-reference and pull out specific data points. Here are some of its main benefits:

• User-friendly: Setting up VLOOKUP can be simple and quick once you understand its syntax.
• Data integrity: It reduces errors that occur when searching and typing data manually.
• Versatility: It can be combined with other Excel functions such as IF, ISERROR, and MATCH for a more dynamic approach.

Improving VLOOKUP with alternative functions

While powerful, VLOOKUP has some limitations, such as looking only at the right side of the lookup column. Alternatives or improvements include HLOOKUP for horizontal searches or the more flexible INDEX and MATCH combination. Here's how you can convert a basic VLOOKUP to INDEX and MATCH:

    =INDEX(C2:C4, MATCH(102, A2:A4, 0))

This formula will achieve the same results as the previous VLOOKUP example, but provides more flexibility, such as looking in both directions.

Final thoughts

Mastering the VLOOKUP function can significantly enhance your ability to effectively analyze data. Whether you're keeping inventory, tracking employees or compiling data from a variety of sources, VLOOKUP remains an indispensable tool in your Excel toolkit. Remember that practice makes perfect, and as you begin to use VLOOKUP regularly, understanding its intricacies will become second nature. As you become more comfortable, challenge yourself to explore its alternatives and see how they can complement or replace VLOOKUP in your data analysis tasks.

  Share This Article

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