Buzz
Utilizing Vlookup Between 2 Files and 2 Sheets is an advanced feature, enabling users to search for linked data across 2 or more different sheets and even different files. This capability streamlines linking files, facilitating easier management and calculations with older files without the need to redo data entry.
For those delving into Excel or seeking to enhance their accounting skills, using Vlookup between 2 files and 2 sheets is the article to elevate your expertise in utilizing Vlookup and other advanced functions for future tasks.
Guide on Using Vlookup Between 2 Sheets.
Firstly, let's employ the Vlookup function between 2 sheets, linking sheets in Excel is not a challenging task. Let's explore the example below and follow along.
Imagine we have an Excel file with 2 Sheets named Employee List Sheet, containing a list of employees with their rankings, yet the bonus amounts are unknown.
The other sheet is named Bonus including bonus rankings and bonus amounts.
The task here is to use the Vlookup function between these 2 sheets to find the corresponding bonus for each employee's ranking.
Step 1: Firstly, you'll enter into cell D3 (the first cell of the bonus column) and then input the formula Vlookup(C3, into it.
With
Step 2: Still within the formula, you just need to click on the Bonus Sheet below, then select the entire 2 columns A, B of this Sheet, and press F4 successively on the values A1:B4 as shown in the image.
Step 3: Returning to Employee List Sheet, you will see the result as above, continue entering 2,1 after that.
With 2 here being the displayed value will be column number 2 from the left on the Bonus Sheet and 1 is the relative value (True), or you can also enter 0 here (False) for more accurate results.
And the result appears as shown below, you can compare to see if using the Vlookup function between 2 sheets is accurate or not, now copy this formula in cell D3 down to the positions below.
And that's it, we have completed using the Vlookup function between 2 sheets, the filled data matches the content on the Bonus Sheet.
Guide on Using Vlookup Between 2 Files
In the previous section, we practiced using the Vlookup function with 2 sheets, but what about 2 files? There's not much difference because Excel has provided extensive connectivity options, especially in Excel version 2016.
Similar to before, but this time, we'll separate them by file. The first file is named Mytour.xlsx with a list of Tet holiday bonuses for employees.
And the second file is named dulieu.xlsx with bonus rankings and corresponding bonus amounts.
And our task is to use the Vlookup function to display bonuses in the Mytour.xlsx file through the dulieu.xlsx file.
Step 1: Similarly, we will enter the formula =Vlookup(C3, With C3 being the first value of the Ranking column, we'll use this column to cross-reference the dulieu.xlsx file.
Step 2: Now, you just need to switch to the dulieu.xlsx file which is open, select 2 columns A and B, then press F4 successively on the values A1:B4 to show the $.
Step 3: Return to the Mytour.xlsx file and you'll see the formula is almost complete. Now you just need to add 2 and 0 at each comma as shown below. 2 here is the displayed value which will be column number 2 from the left in the dulieu.xlsx file and 0 (False) for accurate results.
Press Enter to execute the command, and you'll get the result as shown below.
Copying the command for the data below means we have successfully used the Vlookup function between 2 files.
After reviewing the results and cross-referencing, you will determine whether it's accurate or not. If not, check the formula. If everything is correct, you have completed using the Vlookup function between 2 files.
If you still don't know how to select odd or even pages, you can refer to the article How to Print Odd and Even Pages in Word, Excel, PDF that Mytour shared before.
The examples above will help you better understand using the Vlookup function between 2 files, 2 sheets. Remember to practice regularly with Vlookup and other basic functions in Excel. Additionally, readers should visit Mytour frequently to receive more lessons on basic Excel functions or advanced usage, such as using the Vlookup function between 2 files, 2 sheets in this article.
