Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Match and copy data from other 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 from other 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)
  • original-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 is used as a data source from which the working-datoteka.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


Figure 1.
The original 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 called '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: 
=VALUE(RIGHT($A2;LEN($A2)-18)) . 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. Functions RIGHT and LEN are nested in a function VALUE 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'
=IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;2;FALSE);"")

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'
=IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;3;FALSE);"")

I want to note that the Excel function IFERROR available in Excel 2007 and later. In the formula above you will see 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 function VLOOKUP (col_index_num). This argument should be a number. This number indicates from which 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, then we must combine the functions IF and ISERROR. Of course, here we can use the Excel function COLUMN which is incorporated into the formula.

Formule in Excel 97 - 2003
=IF(ISERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;2;FALSE))=FALSE;VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[source-file.xls]Sheet1'!$A$2:$C$100;2;FALSE);"")

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

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