Ace My Paper - get your Excel tasks done by experts. - makes your homework shine.


Excel tutorials

Copy Data if Match from Another Workbook


Right at the beginning I would note that the separator that separates the arguments within the Excel function can be written as (;) or (,)

How to compare and drag or copy the information provided between two Excel workbooks

If you have a need to copy some data to compare the condition or the search criteria and drag or copy the data from another Excel workbook then make the Excel VLOOKUP function. Here I wish to note that when using the VLOOKUP function advisable to appoint a range of data sources.

For this example I will simulate two Excel workbooks. (both workbooks must be open during operation)

  • source-file.xls (base)
  • working-file.xls

Let's look at an example that the first workbook 'working-file.xls' use so that it enter certain values or text.

The second workbook 'source-file.xls' is used as a data source from which the 'working-file.xls' copy specific data based on the criteria contained in the workbook 'working-file.xls'

So the starting workbook us 'source-file.xls' where is our database, from which using the formula copy specific data, depending on the conditions or criteria.

It looks like as shown in Figure 1 below. (click on Read More)

Figure 1.

The source file as a database copy in Excel

Another Workbook (working-file.xls) where we set the criteria and bring the required data in 'column A'. It looks like as shown below. The criterion is in 'Column A' and we need results in columns 'C' and 'D' in which the set formula. These data in 'C' and 'D' columns are copied from a workbook 'source-file.xls' (which serves as the base).

Figure 2.

Working file in Excel 

The meaning of this tutorial with examples workbooks as follows:

The workbook 'source-file.xls' have a database. Parts of the database we want to copy the workbook named 'working-file.xls, but only those portions of the data which bind to the criteria or conditions that we set in the working file.

The workbook 'working-file.xls' table that we have completed data set the conditions or criteria which will be used to pull Excel or copy the information from the base workbook 'source-file.xls'

You notice in 'Figure 2' above 'column A' where there is a class that is. Number formatted as text. From this number/text we need to extract only the last number (behind the rear minus) because it will be used as criteria/condition. This condition is the key bases in the original workbook. Thereunder (the criteria) we want to excel copied from the original workbook (base) other relevant data in columns 'C' and 'D'.

Extracting the last digit of the number that is formatted as text.

So, in column 'A' is the number formatted as text. We need to extract the text from the last digits after the last minus (dashes). This separation of digits can be done using this formula:


This formula will allocate the last character (in this case numbers) that are located behind the last minus sign (dashes) and using this formula include our criteria/condition. The RIGHT and LEN Functions are nested in a VALUE function to score a number. So the conditions or criteria are the last numbers in the class 'column A'.

Withdrawal or copy data from another workbook which satisfy the condition

Column 'C' using the formula Excel will on the basis of criteria copy data from another workbook 'source-file.xls' (base).

The formula is as follows (these formulas are copied down):

Excel cell 'C2'


Copy this formula to the right to get a result for 'column C' and the number '2' to change the number '3'

Excel cell 'D2'


I want to note that the Excel IFERROR function available in Excel 2007 and later. In the formula above you will see a rectangular parentheses or square brackets [source-file.xls]. They indicate that the respective linked files (Workbook) as a data source. This link is obtained by creating a formula when you click on a cell in another workbook.

These formulas shown above, the cells 'C2' and 'D2' can be written in a different way:

Excel cell 'C2': argument (col_index_num) is number 2

1.=> =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;COLUMN(B1);FALSE);"")

2.=> =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;COLUMN(B:B);FALSE);"")

3.=> =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;COLUMN()-1;FALSE);"")


1. => COLUMN(B1) => the result is column number 2

2. => COLUMN(B:B) => the result is column number 2

3. => COLUMN()-1 => the result is column number 2, because the formula in column C, which is the third (3) consecutive => 3-1=2


Excel cell 'D2': argument (col_index_num) is number 3

1. => =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;COLUMN(C1);FALSE);"")

2. => =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;COLUMN(C:C);FALSE);"")

3. => =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;COLUMN()-1;FALSE);"")


1. => COLUMN(C1) => the result is column number 3

2. => COLUMN(C:C) => the result is column number 3

3. => COLUMN()-1 => the result is column number 2, because the formula in column C, which is the fourth (4) consecutive => 4-1=3

You notice in the formulas above argument Excel VLOOKUP function (col_index_num). This argument should be a number. This number indicates from witch column in the order of Excel should be copied the original data. Instead of this number, we can use Excel COLUMN function which also returns the number of columns. Using 'Excel function Column' it is easier to copy the formula to the right without additional restatements formula.

If you are using older versions of Excel 97-2003, you can not use the IFERROR function, then we must combine the IF function and ISERROR function. Of course, here we can use the Excel COLUMN function which is incorporated into the formula.

Formulas in Excel 97 - Excel 2003



And finally, to summarize all of the above stated. The range of data must be written with absolute addresses or appointed (define name).

In cell 'A2', the condition was last number 1

The formula is set in an Excel cell 'C2' returns the text 'Troja', which is in the second column of the range of data '[source-file.xls]Sheet1'!$A$2:$C$100

The formula is set in Excel 'D2' returns the text 'KAD' which is located in the third column, the range of data '[source-file.xls]Sheet1'!$A$2:$C$100

Also see the link tutorial - How to incorporate or insert another function to VLOOKUP function.

Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.