How to Use VLOOKUP in Excel with Two Sheets

One of the most powerful tools in Microsoft Excel is VLOOKUP, a function that searches for a value in a specified table and returns a related value from the same row. VLOOKUP is often used to join data from different sheets or tables, and it can save hours of manual work. In this blog post, we will show you how to use VLOOKUP in Excel with two sheets, so you can quickly and easily retrieve related data from different parts of your workbook. Whether you’re a beginner or an advanced user, this guide will provide a concise and practical explanation of how to use this feature.

Overview

VLOOKUP stands for Vertical Lookup and it allows you to search for a specific value in a table and return a corresponding value from the same row. This function is extremely useful when you want to join data from different sheets or tables based on a shared value. In this guide, we’ll show you an example of how to use VLOOKUP in Excel with two sheets, using a real-life scenario.

The Scenario: A Sales Report

Imagine that you’ve been asked to create a sales report for your company, which has two spreadsheets:

• The first spreadsheet is called “Sales” and it contains information about all the sales that your company has made.
• The second spreadsheet is called “Products” and it contains information about all the products that your company sells.

Your task is to create a report that shows the total revenue for each product that your company sells. To do that, you need to join the data from the two spreadsheets based on the product name.

Step 1: Organize Your Data

The first step to using VLOOKUP with two sheets is to organize your data properly.

In our example, we’ll assume that the product names are in column A of the “Sales” spreadsheet and column A of the “Products” spreadsheet. We’ll also assume that the revenue for each sale is in column D of the “Sales” spreadsheet and that the price for each product is in column B of the “Products” spreadsheet.

It’s important to note that VLOOKUP only works when the lookup value is in the first column of the table that you’re searching. That means you need to make sure that your lookup values are in the correct column.

Step 2: Create the Formula

Once your data is organized, it’s time to create the formula that will join the data from the two spreadsheets.

To do that, you need to add a new column to the “Sales” spreadsheet, where you’ll enter the VLOOKUP formula. In our example, we’ll add a new column called “Price” to column E, and we’ll enter the following formula in cell E2:

=VLOOKUP(A2,Products!A:B,2,FALSE)

This formula has three arguments:

• The first argument (A2) is the lookup value, which is the product name in our case.
• The second argument (Products!A:B) is the table array, which is the range of cells that you’re searching. In our case, it’s the A and B columns of the “Products” spreadsheet.
• The third argument (2) is the column index number, which is the column number of the value that you want to return. In our case, we want to return the price, which is in the second column of the “Products” spreadsheet.
• The fourth argument (FALSE) tells Excel to do an exact match, which means that it will only return results if the lookup value is an exact match. If you set this argument to TRUE or leave it blank, Excel will return an approximate match.

Once you’ve entered the formula in cell E2 of the “Sales” spreadsheet, you can copy it down to all the other cells in the E column. Excel will automatically update the formula for each row, based on the product name.

Step 3: Calculate the Total Revenue for Each Product

Now that you’ve joined the data from the two spreadsheets, it’s time to calculate the total revenue for each product.

To do that, you can use another Excel function called SUMIF, which allows you to sum the values in a range, based on a condition.

In our example, we’ll add a new column to the “Sales” spreadsheet, called “Total Revenue”, to column F, and we’ll enter the following formula in cell F2:

=SUMIF(A:A,A2,D:D)

This formula has three arguments:

• The first argument (A:A) is the range of cells that you want to search, which is column A of the “Sales” spreadsheet.
• The second argument (A2) is the condition that you want to match, which is the product name in cell A2.
• The third argument (D:D) is the range of cells that you want to sum, which is column D of the “Sales” spreadsheet.

Once you’ve entered the formula in cell F2 of the “Sales” spreadsheet, you can copy it down to all the other cells in the F column. Excel will automatically update the formula for each row, based on the product name.

Conclusion

We hope this guide has been helpful in showing you how to use VLOOKUP in Excel with two sheets. By following these simple steps, you can combine data from different sources and create powerful reports that will save you time and effort. Remember to organize your data properly, create the formula, and calculate the total revenue using the SUMIF function. Good luck!

Examples of How VLOOKUP in Excel with Two Sheets Can Be Used

There are many different applications of VLOOKUP in Excel with two sheets. Here are a few examples:

• Joining tables with a common key, such as customer ID or product code
• Looking up values from a reference table, such as exchange rates or tax rates
• Adding data from one spreadsheet to another, such as contact information or sales data

By using VLOOKUP, you can quickly and easily bring together data from different sources, without having to manually copy and paste information. This can save you time and reduce the risk of errors in your data.

Tips for Using VLOOKUP in Excel with Two Sheets

Here are a few tips that will help you to use VLOOKUP in Excel with two sheets effectively:

• Make sure that your lookup values are in the first column of the table that you’re searching, otherwise VLOOKUP won’t work.
• Use the IFERROR function to handle errors that may occur when using VLOOKUP. For example, you can use the formula =IFERROR(VLOOKUP(A2,Products!A:B,2,FALSE),”Not found”) to display the message “Not found” when the lookup value isn’t found.
• Consider using named ranges to make your formulas easier to read and understand. For example, you could name the range A:B in the “Products” spreadsheet “ProductData”, and then use the formula =VLOOKUP(A2,ProductData,2,FALSE) instead of =VLOOKUP(A2,Products!A:B,2,FALSE).

Conclusion

VLOOKUP is one of the most powerful functions in Microsoft Excel, and it’s especially useful when you need to join data from different sheets or tables. By following the steps outlined in this guide, you can use VLOOKUP to quickly and easily retrieve related data from different parts of your workbook. Don’t be intimidated by the formula – with a little practice, you’ll soon be using VLOOKUP like a pro.

FAQs

Here are answers to some frequently asked questions about using VLOOKUP in Excel with two sheets:

What do I do if my lookup values aren’t in the first column of the table?

If your lookup values aren’t in the first column of the table, you can use the INDEX and MATCH functions instead of VLOOKUP. The formula =INDEX(Table2[Column you want to return],MATCH(Lookup Value,Table2[Lookup Column],0)) allows you to search for a value in a separate table and return a corresponding value from the same row. This function works in the same way as VLOOKUP, but doesn’t require the lookup value to be in the first column.

Can I use VLOOKUP with more than two sheets?

Yes, you can use VLOOKUP with more than two sheets. The process is similar to using VLOOKUP with two sheets – you just need to specify the correct table array and column index number for each sheet.

What should I do if my VLOOKUP formula is returning #N/A?

If your VLOOKUP formula is returning #N/A, it could be because the lookup value isn’t an exact match for any of the values in the table, or because the table array isn’t formatted as a table. Check that the lookup value is correct, and that the table array is formatted correctly, with no blank cells in the first column. You can also try using the IFERROR function to handle errors, as described in the Tips section above.

Can I use VLOOKUP to search for multiple values?

No, VLOOKUP can only search for one value at a time. However, you can use the INDEX and MATCH functions with the CONCATENATE function to search for multiple values. The formula =INDEX(Table2[Column you want to return],MATCH(CONCATENATE(Lookup Value 1,Lookup Value 2),Table2[Lookup Column 1]&Table2[Lookup Column 2],0)) allows you to search for two separate values in the same row.

Are there any alternatives to using VLOOKUP?

Yes, there are several alternatives to using VLOOKUP, including the INDEX and MATCH functions, the HLOOKUP function, and PivotTables. These functions allow you to search for values in a table and return corresponding values from the same row or column. You can experiment with different functions to see which one works best for your data.