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.

  • Please show an example of a few columns and rows of the files, just to understand it better.
    – jcbermu
    May 19, 2015 at 8:31
  • I added a picture so you see the basic idea of the main list, I can't put a snip of the real list as some of it is sensitive information. Now I have compiled the info in the other file to a single cell on each persons row and would need to automatically transfer from name to name to the main file.
    – Slyre
    May 19, 2015 at 8:59
  • What is the structure of the other list? Does it have the employee name? How can you expect us to write a formula if you don't show us what we are working with?
    – teylyn
    May 19, 2015 at 10:07
  • OK, I understand that there is a language issue that contributes to communication mishaps, but with this So now I need a formula that checks if length is >30 and pay is "payless". you have just moved the goal posts to a different playing field. I suggest you abandon this question and start over. It does not help if you change the data and the requirements every hour. So, ask one question for one problem and stick to it. Provide as much detail as you can. Start with a small problem. Then ask another question that builds on the solution. Don't try to do everything at once.
    – teylyn
    May 20, 2015 at 8:50

1 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


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.

enter image description here

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.

  • The absences list is: Employee ID# | Unit | Unit name | Name | # | Absence reason | payment | starting date | ending date | length
    – Slyre
    May 19, 2015 at 10:43
  • I've added an adjusted formula
    – teylyn
    May 19, 2015 at 10:49
  • I keep getting error from excel "There is an error in your formula" and it says in bold "index_number".
    – Slyre
    May 19, 2015 at 11:24
  • I've updated the post with a screenshot that shows the formula works just fine. If it does not for you, make sure you enter it correctly. Do your regional settings require a semicolon instead of a comma, maybe? We really need to drag every bit of information out of you. It's not fun to work like that. I'm going to leave it at that unless you provide substantially more information. In your question. Not in comments.
    – teylyn
    May 19, 2015 at 20:35
  • I apologize for being difficult to work with, I now updated with all the info I think I can give. If you still miss something vital please let me know.
    – Slyre
    May 20, 2015 at 7:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .