In this tutorial, I will show you some really easy way to work on two Workbooks – VLOOKUP With Multiple Workbooks.
Simultaneously with VLOOKUP Function. Many times you need data from different workbook to your desired workbook for calculations so for solving this challenge VLOOKUP function comes into the picture.
VLOOKUP Explanation
The Excel VLOOKUP function which will be used looks like the following.
=VLOOKUP (value, table, col_index, [range_lookup])
Here is brief explanation of what each function does in the formula.
- Value – The value to look for in the first column of a table.
- Table – The table from which to retrieve a value.
- col_index – The column in the table from which to retrieve a value.
- range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.
In its simplest form, the VLOOKUP function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
We will understand VLOOKUP function with some example.Suppose we have two workbook named “Job Salary Records” and “Employee Deductions” and we want to look up for data in salary records from deductions workbook then follow this steps:
Step 1: Arrange Both Workbooks side by side so you can look at both workbook simultaneously for that do this (Click View-Arrange All-Select Vertical-Click Ok).
Step 2:Now our objective is to create new column named “Annual Salary” in Column O of Employee Deductions Workbook.
Step 3:We want to look up for Employee Name John Smith’s annual salary but we know that salary is listed in another in Salary Records workbook and salary is relative to his employee ID number so we will look up at his Employee ID number in other workbook.So I selected John Smith’s employee ID number .
Step 4 : Put Comma Click on another Workbook and Select range of entire array of table which includes Employee ID number and annual salaries that are relative to those ID numbers..
Step 5: Now Click on Deduction’s workbook .You have to select column index number but Excel have index by letter not number but Excel makes its easy by making column A indexed by column 1and so on…
Step 6: Now write Number 9 in formula as index number put comma and go ahead with next text function [range look up].So write FALSE for the exact match and close the bracket.Formula will look like the following:
=VLOOKUP(A2,’[JobSalaryRecords.xlsx]Employee Job Salary’!$A$2:$I$22,9,FALSE]
Step 7 : Press Enter and you will get Annual Salary of John Smith.Drag the fill handle down so you will get all employee’s salary.So Its Done.
VLOOKUP With Multiple Workbooks
In this post we looked at VLOOKUP function used to transfer data between two workbooks. Please leave a comment below If you have any questions or suggestions.I will reach out to you asap.Thank you!
