Comments
How to Use VLOOKUP in Microsoft Excel
Edited 1 week ago by ExtremeHow Editorial Team
Microsoft ExcelFormulasProductivitySpreadsheetBusinessMacWindowsData Management
This content is available in 7 different language
VLOOKUP is one of the most popular functions in Microsoft Excel. This function means "vertical lookup," and it is used to look up data in a vertically arranged table. Whether you're working with a small dataset or a large complex spreadsheet, VLOOKUP can be a powerful tool for finding and retrieving information quickly and accurately.
The VLOOKUP function helps you find values in one column and return values from another column in the same row. This is especially useful when dealing with large amounts of data, where searching manually would be too time-consuming. This article will explain how to use the VLOOKUP function in Excel, including detailed examples to help you understand its powerful capabilities.
Understanding the VLOOKUP function
The syntax of VLOOKUP is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- Lookup_value: This is the value you want to search for. It must be located in the first column of the table you want to search.
- table_array: This is the range of cells that contains the data. It includes the column with the lookup_value and the column with the return value.
- col_index_num: This is the column number in table_array from which the matching value should be returned. The first column is number 1.
- range_lookup: (Optional) A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. If FALSE, VLOOKUP will find an exact match.
Basic example of VLOOKUP
Suppose you have a simple table containing the names of students and their grades. The table is located in the range A2:B5, as shown below:
| Name | Grade | | John | 85 | | Jane | 92 | | Lisa | 78 | | Mark | 88 |
If you want to look up Jane's grade using VLOOKUP, you can use the following formula:
=VLOOKUP("Jane", A2:B5, 2, FALSE)in this instance:
- lookup_value is "Jane".
- table_array is A2:B5.
- col_index_num is 2, because the grades are in the second column of the table.
- Range_lookup is false, meaning you want an exact match.
The formula will return "92", which is Jane's grade.
How to use VLOOKUP with approximate match
There are situations where an exact match cannot be found, or you might want the closest match instead. This is where approximate matching is useful. To allow approximate matching, set the range_lookup argument to TRUE or leave it blank.
Consider another table containing prices of items based on a price list:
| Price List | | Range | Price | | 0-99 | 100 | | 100-199 | 200 | | 200-299 | 300 | | 300-399 | 400 |
If you want to know the cost of purchasing 150 units, set up the formula as follows:
=VLOOKUP(150, A2:B5, 2, TRUE)Here:
- lookup_value is 150.
- The table_array is A2:B5, which contains the lower limit of each range.
- col_index_num is 2, indicating that the value is in the second column.
The formula will return "200" because 150 falls within the range 100-199.
Using VLOOKUP with multiple sheets
VLOOKUP can also be used to find data in different sheets in the same workbook. Imagine you have two sheets, "Products" and "Prices." The "Products" sheet contains a list of product codes, and you want to find the price from the "Prices" sheet.
Product sheet: | Code | | A1 | | B2 | | C3 | Price Sheet: | Code | Price | | A1 | 50 | | B2 | 60 | | C3 | 70 |
In the "Products" sheet, suppose you want to find the price of product code A1. Use the VLOOKUP function as follows:
=VLOOKUP(A2, Prices!A2:B4, 2, FALSE)Here:
- The lookup_value is cell A2, which contains the code "A1".
- table_array refers to the "Values" sheet with values!A2:B4.
- col_index_num is 2 to find the price column.
- range_lookup is false for exact match.
This formula will return the value "50" for the product code A1.
Common errors and troubleshooting
While VLOOKUP is a powerful tool, it's common to encounter errors when using it. Here are some common errors and how to fix them:
- #N/A Error: This occurs when VLOOKUP cannot find a match for the lookup_value. Make sure the lookup_value exists in the first column of the table_array. Double-check the spelling and format of the lookup_value to make sure they match those in the data range.
- #REF! Error: This error occurs when col_index_num is greater than the number of columns in the table_array. Check the table_array range and col_index_num to make sure you are referencing the correct column.
- #VALUE! error: This can happen if any of the arguments is incorrect, such as if col_index_num is text instead of a number. Double-check the formula syntax to make sure all the values are correct.
Improving VLOOKUP efficiency
VLOOKUP can also be combined with other functions to increase its use and efficiency. For example, combining VLOOKUP with IFERROR can help handle errors more gracefully. Instead of showing an error, you can return a custom message:
=IFERROR(VLOOKUP("Lisa", A2:B5, 2, FALSE), "Not Found")If VLOOKUP doesn't find a value this formula will return "Not Found" instead of an error.
Another way to increase VLOOKUP efficiency is to use the CHOOSE function when working with non-contiguous data. The CHOOSE function helps to instantly create a virtual range:
=VLOOKUP("B2", CHOOSE({1,2}, Indirect("Sheet1!B:B"), Indirect("Sheet3!D:D")), 2, FALSE)This example shows how VLOOKUP works by virtually creating a table to work with within data that is scattered in non-adjacent columns.
Conclusion
VLOOKUP is a versatile and useful tool, fundamental to anyone who uses Excel for data manipulation and analysis. Once you understand how VLOOKUP can find and return data accurately and efficiently in a variety of contexts, you'll find it much easier to solve complex lookup needs. Practice using VLOOKUP with a variety of datasets and apply the concepts in this article to become proficient in its use.
If you find anything wrong with the article content, you can