How to fetch data from another excel file and sort it under name?

Question

I have 2 excel files, one of them is a list of workers and the other is a list of absences. I need to have the reason and the ending date from the absences list to be fetched into the main list, and put it under the right name. Right now I have to do it manually, but since there are 1000+ names on the main list and 800ish names on the absence list, it gets really old really fast.

E: Alright, lets try this again.

Mainlist formatting:

    A     | B  |    C    |   D    |    E    |      F      |     G     |
Basic unit|Name|Birthdate|Position|Permanent|starting date|ending date|

Absences formatting:

     A       |  B   |    C      |  D   | E |      F         |    G    |     H         |     I       |     J      
Employee ID# | Unit | Unit name | Name | # | Absence reason | payment | starting date | ending date | length

And today I got more detailed sorting criteria, I need to add absences to main list from absences if the length is over a month and without pay and when those criteria are fulfilled I need

Absence reason, starting date - ending date

to be added to mainlists H2 and downwards on the corresponding name. Now I can do it so that I make an extra column on the absences list where I combine the needed info into K2:

=F2&", "& TEXT(A1,"dd/mm/yy")&" - "&TEXT(B1,"dd/mm/yy")

So now I need a formula that checks if length is >30 and pay is "payless".

Also I am Finnish which means my Excel is in finnish which means I have to run the formulas through a translator, currently I am using http://en.excel-translator.de/translator/

E2: I sorted the absences list so that only the ones I need are left, so I don't need to check for anything just to fetch and place on the corresponding name on the main list.

Answer 1

Since you don't show the absence list, let's assume it contains the columns

Name | Start Date | End Date | Absence Reason

Add two columns to your Employee sheet and use vlookup like this

=iferror(vlookup(A2,[Absences.xls]Sheet1!$A$1:$D$1000,3,false),"")

This will return the End date if the name in cell A2 can be found in the Absence list. For the Absence Reason use a 4 as the third parameter of Vlookup

Edit: With the column structure provided in the comment

     A       |  B   |    C      |  D   | E |      F         |    G    |     H         |     I       |     J      
Employee ID# | Unit | Unit name | Name | # | Absence reason | payment | starting date | ending date | length

and assuming that the first column is in column A, and assuming that the Name column contains matching names, the formula for Absence reason is

=iferror(vlookup(A2,'[the file.xlsx]the sheet'!$D$1:$I$1000,3,false),"")

and for ending date

=iferror(vlookup(A2,'[the file.xlsx]the sheet'!$D$1:$I$1000,6,false),"")

Adjust file and sheet name to suit.

Keep in mind that Vlookup uses the first column of the lookup table to find a match, so we start the lookup table in column D, which holds the names. Vlookup works to the right.

If you want to return data to the left of the Name column then you'd need a different formula.