How to use Vlookup function between 2 sheets, 2 different Excel files

The Vlookup function is a fairly common and frequently used function on Excel spreadsheets. Normally the Vlookup function applies on the same file in a certain workbook, but in some cases where the work is not so simple, the condition area and the area you need to access the data belong to belongs to two other sheets. each other, even further, can be two different excel files. However, you can still perform normal access with the instructions for each item below.

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 1

Apply Vlookup function between 2 different sheets

Suppose we have 1 Excel file of 2 sheets: Summary and Unit Price . The task you need is to use the Vlookup function to retrieve data from the Unit Price Sheet to the Summary Sheet .

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 2

In cell F3 of the Sum sheet, type the formula = Vlookup (D3,

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 3

Keep the formula just entered, select the Unit price sheet and select the conditional data range A2: B5 then press the F4 button to absolute position the conditional data range. The formula will become: = Vlookup (D3, 'Unit price'! $ A $ 2: $ B $ 5

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 4

At the formula setting, enter the formula: " , 2, TRUE )" and press the Enter button .

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 5

Then drag the formula copy below the remaining cells in the column will display all the results:

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 6

Apply Vlookup function between 2 files

Along with the above requirements, but the data is not in 2 sheets but 2 different files, so how do you enter the formula?

Step 1: Open 2 Excel files .

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 7

Step 2: In cell F3 of the Revenue file the same formula applies to the above 2 sheets: = Vlookup (D3,

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 8

Step 3: Convert to Unit price , select the data area A2: B5 => click F4 to fix the area A2: B5 .

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 9

Step 4: Return to the Summary file , continue the formula: ", 2, TRUE )" and press Enter .

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 10

Step 5: Copy the formula and produce all the results of the remaining cells in the column.

How to use Vlookup function between 2 sheets, 2 different Excel files Picture 11

Above, Dexterity Software showed how to use the Vlookup function between 2 files and 2 sheets in excel, hoping to help you. Have a good day!

4 ★ | 1 Vote

May be interested

  • How to compare data on 2 different sheets in Excel fileHow to compare data on 2 different sheets in Excel file
    to compare data on 2 different sheets but in the same excel file, we have many different methods such as using vlookup or using the countif function and combining with conditional formating to create different comparison effects. or no difference in data in two different sheets.
  • How to use the XLOOKUP function in Excel?How to use the XLOOKUP function in Excel?
    excel's new xlookup function replaces vlookup, which provides a powerful replacement for one of excel's most popular functions. this article will guide you how to use this xlookup function.
  • How to use Hlookup function on ExcelHow to use Hlookup function on Excel
    hlookup function basically also has the function syntax and features like vlookup function, which is to help users find data in excel table, with the conditions or given information. here is the guide for using the hlookup function in detail.
  • Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCHLook up data in Excel tables: Replace VLOOKUP with INDEX and MATCH
    quick lookup of data in excel tables: replace vlookup with index and match. do you know yet? let's tipsmake.com find out in the article below!
  • VLOOKUP function - Usage and detailed examplesVLOOKUP function - Usage and detailed examples
    vlookup is one of the most used excel functions. the following article details how to use and detailed examples when using the vlookup function.
  • 30+ useful Google Sheets functions30+ useful Google Sheets functions
    google sheets is a great alternative to microsoft excel. it's packed with useful features, including a wide range of functions that cover most of those available on excel and more.
  • How to filter duplicate data from 2 Sheets in ExcelHow to filter duplicate data from 2 Sheets in Excel
    how to filter duplicate data from 2 sheets in excel. in excel file, you have data in 2 different sheets but you want to filter duplicate data with a large number of records. here's how to use the vlookup function to filter duplicate data from 2 sheets in exc
  • How to combine Vlookup function with Left functionHow to combine Vlookup function with Left function
    when combining the vlookup function with the left function, we will easily separate the values ​​to find the right information we need.
  • How to use the IF function with VLOOKUP (examples and how to)How to use the IF function with VLOOKUP (examples and how to)
    how to use the if function in combination with vlookup (examples and how to do it). if you are looking to use the if function with vlookup with specific examples, please refer to the following article. the following article will guide you 3 common cases using the if function
  • How to quickly delete multiple sheets in ExcelHow to quickly delete multiple sheets in Excel
    working with sheets in excel involves the very basics of working with excel tables. with an excel sheet we have operations like adding sheets, changing sheet colors or deleting sheets when not in use.