How to Use VLOOKUP Formula in Excel (Comprehensive Guide)

As one of the most powerful and versatile functions in Excel, the VLOOKUP function has become a go-to tool for countless users. It simplifies data retrieval, making it easier than ever to find and extract the information you need from your Excel spreadsheets. In this article, we'll explore the VLOOKUP function in depth, covering what it is, understanding its syntax, when to use it, and how to insert it into your Excel workbook.

‍

What is the VLOOKUP function in Excel used for?

The VLOOKUP (also known as Vertical Lookup) function in Excel is used to search for a specific data point in a vertical column and return a value from another column corresponding to the same data point.

For example: If you have a list of products in a product catalog with their corresponding selling prices for every month from the year 2009 to 2022. The VLOOKUP formula can help you easily find the price for a particular product in June 2013. 

‍

Understanding the VLOOKUP formula syntax in Excel

The VLOOKUP formula consists of four arguments:

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

‍lookup_value: The value you want to search for in the first column of the table_array. 

table_array: The range of cells that make up your data table in Excel, including the column where you want to find the lookup_value and the column containing the data you want to retrieve. This is also commonly referred to as the lookup array.

col_index_num: The column number in the table_array where the data you want to retrieve is located. The first column of the table_array has a col_index_num of 1, the second column has a col_index_num of 2, and so on.

[range_lookup]: An optional argument that indicates whether you want an exact match (FALSE) or an approximate match (TRUE) for the lookup_value. If omitted, it defaults to TRUE.

‍

When to use VLOOKUP function in Excel

The VLOOKUP formula is ideal for situations when you need to:

• Consolidate data from multiple sources: If you have data in different Excel tables or sheets that need to be combined based on a common identifier, the VLOOKUP formula is a great solution.
• Compare lists: The VLOOKUP function in Excel can help you identify discrepancies between two lists, such as comparing pricing information or checking for missing data.‍
• Extract information from large datasets: With VLOOKUP, you can quickly find and retrieve specific data points within a vast dataset in Excel workbooks, saving time and effort.

‍

Steps to inserting the VLOOKUP formula in Excel

Follow these steps to insert the VLOOKUP formula in your Excel workbook:

Step 1: Select the cell where you want to use the VLOOKUP formula and retrieve the result.

Step 2: Type “=VLOOKUP” and select the VLOOKUP formula prompt that shows up.

Step 3: Once the formula syntax “=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])” shows up, replace the arguments with your specific values as below: 

• Replace ‘lookup_value’ with the value or cell reference you want to search for.

For example: Consider that you have a Profit and Loss statement for the year 2022 but you want to create a dashboard showing only Total Income, Total Cost of Goods Sold and Gross Profit for Q1 2022. In such a case, the VLOOKUP function can be used to quickly arrive at the required dashboard as below.

• Replace ‘table_array’ with the range of cells that make up your data table. The ‘lookup_value’ must always be in the first column of your selected data table.

• Replace ‘col_index_num’ with the column number of the column that contains the data corresponding to the ‘lookup_value’ that you want to retrieve.

• Replace ‘[range_lookup]’ with either TRUE (approximate match) or FALSE (exact match), or leave it out if you want it to default to TRUE. 

Step 4: Press the “Enter” key to complete the formula, and Excel will display the result.

‍

Note 1: The VLOOKUP formula in Excel can be used to pull data across different Excel worksheets and workbooks.

‍Note 2: If the lookup_value is not present in the first column of the table_array, Excel will display the result as “#N/A”.

‍

Hope this guide has helped you gain a better understanding of the VLOOKUP function in Excel.