Master Excel VLOOKUP: Multiple Workbooks

Table of Contents

1. Introduction
2. Downloading the Required Excel Workbooks
3. Arranging Excel Workbooks Side-by-Side
4. Creating a New Column for Annual Salary
5. Using VLOOKUP Function
6. Selecting the Lookup Value
7. Identifying the Table Array
8. Indicating the Column Index Number
9. Choosing Exact Match
10. Applying the Function to the Rest of the Worksheet
11. Formatting the Salary Values

Introduction

In this tutorial, we will explore how to utilize the VLOOKUP function in Microsoft Excel when working with multiple workbooks. Specifically, we will focus on using VLOOKUP function to extract annual salary information from a separate Excel workbook Based on employee ID numbers. By following the step-by-step instructions provided, You'll be able to effectively use VLOOKUP function and populate the annual salary column in your employee deductions workbook.

Downloading the Required Excel Workbooks

Before we begin, you will need to download two Excel workbooks from a Google Drive folder. You can access the download link by clicking here. Make sure to download both the "Employee Deductions" and "Job Salary Records" workbooks. Once downloaded, we can proceed to the next step.

Arranging Excel Workbooks Side-by-Side

To facilitate easy comparison and data entry, we will arrange the two Excel workbooks side-by-side. Follow these steps to achieve this:

1. Open both the "Employee Deductions" and "Job Salary Records" workbooks.
2. Click on the "View" tab in the Excel ribbon.
3. Select "Arrange All" from the "View" menu.
4. In the Arrange Windows dialog box, choose the "Vertical" option.
5. Click "OK".

Now, the two workbooks should be arranged vertically side-by-side, making it convenient to refer to both while working.

Creating a New Column for Annual Salary

The objective is to Create a new column in the "Employee Deductions" workbook to display the annual salary information. To do this, follow these steps:

1. Locate the appropriate column where you want to add the annual salary. Let's say we choose column O.
2. Enter the heading "Annual Salary" in cell O1.

Now, We Are ready to use the VLOOKUP function to populate the annual salary values for each employee.

Using VLOOKUP Function

The VLOOKUP function allows us to search for a value in one column of a table and retrieve a value in the same row from another column of that table. In our case, we will use VLOOKUP to look up the employee ID numbers in the "Job Salary Records" workbook and retrieve their corresponding annual salaries.

Selecting the Lookup Value

In the VLOOKUP function, we start by indicating the value we want to look up. In our case, we are looking up the annual salary of John Smith. Here's how to proceed:

1. In cell O2 (next to John Smith's employee ID), enter the following formula: =VLOOKUP(A2, '[Job Salary Records]Sheet1'!$A$2:$I$100, 9, FALSE).
2. Press Enter.

The VLOOKUP function will search for John Smith's employee ID (in cell A2) within the range specified in the "Job Salary Records" workbook. It will retrieve the corresponding annual salary from column I.

Identifying the Table Array

The table array is the range of cells where the VLOOKUP function should search for the lookup value. In our case, we need to select the entire table array in the "Job Salary Records" workbook. Here's how:

1. Click on the "Job Salary Records" workbook to activate it.
2. Select the range of cells from A2 to I100 (adjust the range based on your data).

By selecting this range, we are instructing Excel to search for the lookup value in the employee ID number column (A2:A100) and retrieve the corresponding annual salary from column I.

Indicating the Column Index Number

The column index number tells Excel which column from the table array contains the values we want to retrieve. In our case, we want to pull the annual salary from column I. Here's how to indicate the column index number:

1. In the VLOOKUP function formula, change the number 9 to 9 (representing column I).

This ensures that the VLOOKUP function retrieves the data from column I of the table array.

Choosing Exact Match

To ensure that the VLOOKUP function only returns an exact match, we need to specify FALSE as the last argument. Here's how:

1. In the VLOOKUP function formula, change FALSE to FALSE.

By selecting FALSE, we are instructing Excel to only return an exact match of the employee ID number.

Applying the Function to the Rest of the Worksheet

Once we have successfully entered the VLOOKUP function for John Smith, we can Apply the same formula to the rest of the employee rows in a relative manner. Here's how:

1. Select the cell O2 (where the VLOOKUP function formula is entered).
2. Place the Cursor over the bottom-right corner of the selected cell until it turns into a solid black plus sign.
3. Click and hold the cursor, then drag it down to the last employee row where you want to apply the formula.

This action applies the same VLOOKUP function formula to all the selected cells, adjusting the lookup value and retrieving the corresponding annual salary for each employee.

Formatting the Salary Values

At this point, the annual salary values might not be formatted in a desirable manner. We can format them to match the existing number formatting used in the "Job Salary Records" workbook. Here's how:

1. Select the cells containing the populated annual salary values.
2. Go to the "Home" tab in the Excel ribbon.
3. Click the "Number" dropdown in the "Number" group.
4. Select "Number" to open the "Format Cells" dialog box.
5. Choose the desired number formatting options, such as decimal places and thousands separator.
6. Click "OK" to apply the formatting.

Now, the salary values should be formatted consistently with the rest of the numbers in the "Job Salary Records" workbook.

Conclusion

Congratulations! You have learned how to use the VLOOKUP function in Excel to retrieve annual salary information from a separate workbook. By following the steps outlined in this tutorial, you can effectively populate the annual salary column in your employee deductions workbook based on employee ID numbers.

If you have any questions or need further assistance, please leave a comment below. We are here to help you!

Highlights

• Utilize the VLOOKUP function in Excel to extract annual salary information from a separate workbook.
• Download and arrange multiple Excel workbooks side-by-side for easy comparison.
• Create a new column in Excel to display the annual salary values.
• Use the VLOOKUP function to search for employee ID numbers and retrieve corresponding salary information.
• Apply the VLOOKUP function in a relative manner to populate the entire column.
• Format the salary values to match the desired number formatting.

Frequently Asked Questions

Q: Can I use VLOOKUP function to retrieve information from multiple worksheets within the same workbook?

A: Yes, the VLOOKUP function can be used to retrieve information from multiple worksheets within the same workbook. Simply specify the worksheet name followed by an exclamation mark (!) in the table array argument of the VLOOKUP function.

Q: What can I do if the VLOOKUP function is returning incorrect values?

A: If the VLOOKUP function is returning incorrect values, there are a few potential issues to check:

• Ensure that the lookup value is consistent and correctly entered.
• Double-check the table array range to make sure it covers the correct data.
• Verify that the column index number corresponds to the desired column in the table array.
• Confirm that the last argument (exact match or approximate match) is set appropriately.

Q: Can I use VLOOKUP to search for values in a different Excel file altogether?

A: Yes, you can use VLOOKUP to search for values in a different Excel file by specifying the full path and filename of the external workbook in the table array argument. However, make sure that the external workbook is open or accessible at the specified location.

Q: Are there any alternatives to the VLOOKUP function for retrieving data from multiple workbooks?

A: Yes, there are other functions in Excel, such as INDEX-MATCH, that can also be used to retrieve data from multiple workbooks. These functions provide more flexibility and control in matching and retrieving data based on specific criteria.