How to Use VLOOKUP Formula in Excel? (Example Between Two Sheets Included!)

Are you wondering how to use the VLOOKUP formula in Excel to look up data between two sheets? VLOOKUP is a powerful Excel function that allows you to retrieve information from one sheet and use it in another. In this step-by-step guide, we’ll walk through how to use VLOOKUP with an example using two Excel sheets.

What is VLOOKUP in Excel?

VLOOKUP (Vertical Lookup) is a built-in Excel function used to look up and retrieve data from a specific column in a table based on a given value. It searches for the value vertically down the first column of the table until it finds a match, and returns the corresponding value from another specified column.

The VLOOKUP function is incredibly useful when you need to combine or link data between two different tables or sheets based on a common value or identifier. Some common scenarios where VLOOKUP excels include:

• Merging data from separate sources: If you have customer information in one spreadsheet and sales data in another, VLOOKUP can help combine the datasets using a unique customer ID.
• Looking up product details: With a product catalog in one sheet and an inventory list in another, VLOOKUP can retrieve product names, descriptions, or prices into the inventory sheet based on product codes.
• Translating codes to names: If you have a sheet with category codes and another with category names, VLOOKUP can translate the codes into their corresponding names.
• Populating missing information: When data is spread across multiple sheets, VLOOKUP can fill in gaps by pulling information from one sheet into another based on a common identifier.

VLOOKUP Syntax and Arguments

The syntax for the VLOOKUP function in Excel is:

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

It takes the following arguments:

• lookup_value: The value to look for in the first column of the table. This can be a cell reference or a specific value enclosed in quotation marks.
• table_array: The table or range that contains the data you want to retrieve. It can be a range on the same sheet or a named range referring to another sheet.
• col_index_num: The column number in the table from which you want to retrieve the corresponding value. The first column of the table is column 1.
• [range_lookup]: Optional. Enter FALSE for an exact match or TRUE for an approximate match. If omitted, the default is TRUE.

It’s important to understand how each argument works to effectively use VLOOKUP:

• The lookup_value should be in the first column of the table_array. VLOOKUP always searches in the leftmost column.
• The table_array must have the lookup column as the leftmost column. It’s helpful to include the header row in the range for clarity.
• Col_index_num starts at 1 for the first column of the table_array, not the worksheet column number. If you specify a col_index_num greater than the number of columns in the table_array, VLOOKUP will return a #REF! error.
• [Range_lookup] controls whether VLOOKUP finds an exact or approximate match. FALSE requires an exact match, while TRUE allows for the closest match less than or equal to the lookup_value. If the lookup_value is smaller than all values in the first column, VLOOKUP will return #N/A.

Step-by-Step Example: Using VLOOKUP Between Two Sheets

Let’s walk through a concrete example of using VLOOKUP to look up data from one Excel sheet in another. Suppose we have two sheets:

1. “Products” sheet containing product information
2. “Sales” sheet containing sales data for each product

The “Products” sheet looks like:

The “Sales” sheet looks like:

Our goal is to use VLOOKUP to bring the Product Name and Price from the “Products” sheet into the “Sales” sheet based on the Product ID match.

Step 1: Set Up the Lookup Value

In the “Sales” sheet, the common value we want to use for looking up product names and prices is in the “Product ID” column. This will be our lookup_value in the VLOOKUP formula.

Step 2: Define the Table Array

Next, we need to specify the table or range in the other sheet that contains the data we want to retrieve. In this case, it’s the “Products” sheet.

To make the VLOOKUP formula more flexible, let’s name the range. Select the entire data table in the “Products” sheet, including headers, and define a named range called “ProductTable”.

This way, if rows get added or deleted from the Products table later, the named range will automatically adjust and our VLOOKUP will still work.

Step 3: Determine the Column Index Numbers

In the ProductTable range, identify which columns contain the data you want to retrieve and return to the “Sales” sheet.

Remember, VLOOKUP always looks in the leftmost column for the lookup value, which is Product ID in our example.

We want to return the Product Name and Price. In the ProductTable, Product Name is in the 2nd column (col_index_num 2) and Price is in the 4th column (col_index_num 4).

Step 4: Decide on Exact or Approximate Match

The last VLOOKUP argument [range_lookup] determines whether you want an exact or approximate match.

For our example, we want the Product Name and Price to exactly match the Product ID, so we will use FALSE for an exact match.

Step 5: Build the VLOOKUP Formulas

We’re now ready to construct the VLOOKUP formulas in the “Sales” sheet.

In cell E2 (or wherever you want the Product Name to appear), enter:

=VLOOKUP(D2,ProductTable,2,FALSE)

In cell F2 (for the Price), enter:

=VLOOKUP(D2,ProductTable,4,FALSE)

Here’s what each part of the formula means:

• D2 is the cell containing our lookup_value, the first Product ID on the Sales sheet
• ProductTable is the table_array, the named range of the data in the Products sheet
• 2 and 4 are the col_index_num values, indicating we want to return data from the 2nd and 4th columns of the ProductTable (Product Name and Price)
• FALSE is the [range_lookup] for an exact match

Step 6: Copy the Formulas Down the Columns

After entering the VLOOKUP formulas in the first row, double-click the small square at the bottom-right corner of each cell to auto-fill the formulas down their respective columns for the remaining rows.

The VLOOKUP function will automatically adjust for each row, looking up the Product ID and returning the corresponding Product Name and Price from the “Products” sheet.

The end result will be the “Sales” sheet with two new columns showing the Product Name and Price for each sale:

Troubleshooting Common VLOOKUP Errors

If your VLOOKUP formula isn’t working as expected, here are some common issues and solutions:

#N/A Error

If you see #N/A in the result, it typically means the lookup_value wasn’t found in the first column of the table_array. Double check that the lookup value exists and the table range is correct.

Possible fixes:

• Verify the spelling and formatting of the lookup value match the data in the table exactly
• Make sure the lookup value is located in the first column of the table_array range
• Check that the table_array range is properly defined and includes all necessary data

#REF! Error

A #REF! error indicates an invalid cell reference, likely because the col_index_num is greater than the number of columns in the table_array. Make sure the specified col_index_num is within the table range.

To resolve:

• Count the number of columns in your table_array range
• Ensure the col_index_num doesn’t exceed the total number of table_array columns
• If you’ve inserted or deleted columns in the table_array, adjust the col_index_num accordingly

#VALUE! Error

#VALUE! appears when one of the arguments is the wrong data type, such as text entered instead of a number or cell reference. Verify the formula arguments are the correct data types.

Check:

• The lookup_value is a valid cell reference or a value enclosed in quotation marks
• The table_array is a proper range reference (e.g. A1:D10) or named range
• The col_index_num is a number, not text
• The [range_lookup] is either TRUE, FALSE, or omitted

Incorrect Results

If the VLOOKUP returns incorrect or unexpected results, possible reasons include:

• The table_array range doesn’t include all necessary data
• [range_lookup] is TRUE and returning an approximate instead of exact match
• Data formatting inconsistencies between the lookup_value and first table_array column

Review the table range, match type, and data formatting to resolve incorrect results. Ensure:

• The table_array covers the full data range needed for successful lookups
• Exact matches are used when needed by setting [range_lookup] to FALSE
• Lookup and table values are formatted consistently as numbers, dates, or text

Tips for Using VLOOKUP Effectively

Here are some tips to make the most of VLOOKUP in Excel:

1. Use absolute cell references for the table_array if you plan to copy the formula to other cells. This locks the table range so it doesn’t shift when filled down or across. To make a reference absolute, add $ before the column letter and row number (e.g., $A$1:$D$10).
2. Create named ranges for frequently used lookup tables. This makes formulas easier to understand and maintain. To define a named range, select the table data, go to the Name box (left of the formula bar), type a name, and press Enter.
3. Ensure consistent data formatting between the lookup_value and first table_array column to avoid unexpected results. For example, if the lookup value is a number, make sure the corresponding table column is also formatted as numbers.
4. If you need to look up values to the left of the matched column, use INDEX and MATCH functions instead, as VLOOKUP can only look to the right. INDEX returns a value from a specific row and column in a range, while MATCH locates the position of a value in a range.
5. For large datasets, consider using XLOOKUP or Power Query instead of VLOOKUP for better performance and flexibility. XLOOKUP is a newer function that can look in any direction and return values from multiple columns. Power Query is a data transformation and loading tool that can merge tables based on common columns.
6. Break complex formulas into smaller steps for easier troubleshooting and auditing. Instead of nesting multiple functions, calculate intermediate results in separate columns and reference those in your final formula.
7. Document your formulas by adding comments to cells explaining what the formula does and how it works. This helps others (and your future self) understand the purpose and logic behind the calculation.

Real-World Applications of VLOOKUP

VLOOKUP is a versatile function used across various industries and business functions. Here are some real-world examples of how VLOOKUP simplifies data management and analysis:

• Sales and Marketing: Combine customer data from a CRM system with sales transactions to analyze purchase history, segment customers, and personalize marketing campaigns.
• Human Resources: Merge employee records from an HR database with timesheet or payroll data to calculate wages, benefits, and performance metrics.
• Inventory Management: Link product SKUs from an inventory list with supplier information, pricing, and stock levels to optimize ordering and inventory tracking.
• Financial Analysis: Connect account codes from a chart of accounts with financial transactions to categorize expenses, generate financial statements, and monitor budgets vs. actuals.
• Project Management: Map project IDs from a project list with task details, resource assignments, and timelines to track progress, allocate resources, and report on project status.

By automating data lookups and consolidation, VLOOKUP saves time, reduces errors, and enables more comprehensive data analysis across departments and systems.

Final Thoughts

VLOOKUP is a versatile and essential Excel function for looking up information from one dataset and connecting it with another based on a common value. By following the steps outlined in this guide and understanding the syntax and arguments, you can easily use VLOOKUP to combine data between two Excel sheets.

Mastering VLOOKUP will save you time and enable you to perform complex data analysis tasks more efficiently in Excel. Practice using the function with your own datasets to solidify your understanding.

For more advanced lookup and data manipulation needs, explore other functions like INDEX, MATCH, XLOOKUP, and Power Query. Keep expanding your Excel skills to make the most of this powerful spreadsheet tool.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.