VLOOKUP formula in Excel with example

WHAT IS VLOOKUP 

(Vertical Lookup) is a powerful Excel function that helps you search for a value in the first column of a range (or table) and return a corresponding value from another column in the same row. It’s most useful when you have large datasets and want to quickly retrieve information.

---

VLOOKUP Formula Syntax:

Where:

• lookup_value: The value you want to search for in the first column.
• table_array: The range of cells that contains the data (including the column where the lookup value is located).
• col_index_num: The column number (from the table array) from which to return the result.
• [range_lookup]: FALSE for an exact match, TRUE for an approximate match.

---

Example 1: Basic VLOOKUP (Exact Match)

Let’s consider the following table where you have Product IDs in Column A and their corresponding prices in Column B. We want to find the price of a product based on its Product ID.

In this case, if you want to find the price of Product ID 102, you can use VLOOKUP.

Formula:

=VLOOKUP(102, A2:B5, 2, FALSE)

Explanation:

• lookup_value: 102 (the Product ID you're looking for)
• table_array: A2:B5 (the range that includes both the Product IDs and Prices)
• col_index_num: 2 (the second column contains the price)
• [range_lookup]: FALSE (for an exact match)

Result in Excel:

$75

How It Looks in Excel

Example 2: Using a Cell Reference for Lookup Value

Instead of directly inputting the lookup value, you can reference a cell. Let’s say the Product ID is in Cell C2.

Formula:

=VLOOKUP(C2, A2:B5, 2, FALSE)

If C2 = 103, the formula will return $100.

How It Looks in Excel

Example 3: VLOOKUP with Approximate Match

VLOOKUP can also perform an approximate match. Let’s say you have a table of exam scores, and you want to assign a grade based on a student's score.

Now, if a student scores 75, we want to determine the grade.

Formula:

=VLOOKUP(75, A2:B6, 2, TRUE)

Explanation:

• lookup_value: 75 (the student's score)
• table_array: A2:B6 (the range that contains the scores and grades)
• col_index_num: 2 (we want to return the grade)
• range_lookup: TRUE (for an approximate match)

Result:

B

How It Looks in Excel

Example 4: Handling Errors with VLOOKUP

Sometimes, VLOOKUP might not find the value you're looking for, resulting in a #N/A error. You can handle this using the IFERROR function.

Let’s say you want to find the price of Product ID 105, which does not exist in your table.

Formula:

=IFERROR(VLOOKUP(105, A2:B5, 2, FALSE), "Not Found")

This formula will return "Not Found" instead of the #N/A error.

How It Looks in Excel

---

Conclusion

The VLOOKUP function is a great tool for efficiently searching and retrieving data in Excel. By understanding how to use its different parameters and combining it with other functions like IFERROR, you can make your Excel tasks easier and more dynamic.