Using VLOOKUP in Microsoft Excel (Step-by-Step)
The VLOOKUP function in Excel is a powerful tool for finding and retrieving data from a table. It allows you to search for a value in the leftmost column of a range or table and return a corresponding value from a specified column in the same row. This tutorial will guide you through the steps of using VLOOKUP effectively.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments:
lookup_value: The value you want to search for in the leftmost column of the table.
table_array: The range or table containing the data you want to search.
col_index_num: The column number in the table from which you want to retrieve the data. The first column is 1, the second column is 2, and so on.
range_lookup (optional): This argument is either TRUE or FALSE (or 1 or 0). It specifies whether you want an approximate match (TRUE) or an exact match (FALSE). If omitted, it defaults to TRUE.
Using VLOOKUP step by step:
For this example, let's say we have a table of products and their corresponding prices. We want to find the price of a specific product using the VLOOKUP function.
- Prepare your data:
Create a table that contains the data you need. For this tutorial, let's assume you have the following table in cells A1 to B5:
| Product | Price |
|--------------|-------|
| Apple | 1.00 |
| Banana | 1.20 |
| Orange | 0.80 |
| Grapes | 2.00 |
| Watermelon | 3.50 |
- Enter the VLOOKUP formula:
In a different cell, where you want to display the result, enter the VLOOKUP formula. For example, if you want to find the price of "Banana," you can enter the following formula in cell D2:
=VLOOKUP("Banana", A1:B5, 2, FALSE)
This formula looks for the value "Banana" in the leftmost column (column A) of the table (A1:B5) and retrieves the corresponding value from the second column (column B).
- Press Enter:
After entering the formula, press Enter. The result will display the price of the product "Banana," which is 1.20.
Tips:
Make sure the data in the leftmost column of the table is sorted in ascending order when using an exact match (FALSE) for better accuracy.
If the VLOOKUP cannot find the lookup_value in the table (e.g., if the product name is misspelled or doesn't exist), it will return an error (#N/A).
You can use cell references for the lookup_value instead of directly entering the value in the formula. For example, you can have the product name "Banana" in cell A10 and use =VLOOKUP(A10, A1:B5, 2, FALSE) to get the price.
VLOOKUP is a versatile function that can be used in various scenarios, such as retrieving data from large datasets, merging information from different tables, and creating dynamic reports. By mastering VLOOKUP, you can efficiently analyze and manage data in Excel.
