Matching an id from a sheet then copying correspondent value from a second sheet

[Andreas_Zopo]

Hi there,

Yesterday I asked a very similar question but tried using a variation of the given formula for this case and didn't work. (Sorry for asking too much)

I have a sheet called 'HOURS' with 1344 employees with their EmployeeID on column A. I have another sheet called 'DOB' with 1296 employees with also their EmployeeID on column A.

The first sheet is missing the DOB which, surprisingly enough, is contained within the DOB sheet. The problem is both employee lists aren't consistent and not sorted alphabetically. Also, the list 'HOURS' still has employees that are no longer employed.

I need to take the EmployeeID from 'HOURS', match it with its correspondent value in 'DOB', copy the Date of Birth, then write it back in the empty DateOfBirth column in 'HOURS'. In both sheets the value DateOfBirth is in column F.

John gave me this formula yesterday =VLOOKUP(DATEDIF($D6,TODAY(),"y"),CalChoice!$A$3:$C$54,2,1) which I tried to modify to meet this new requirement but of course I failed.

Any help would be appreciated.

Thanks!

[daffodil11]

Assuming DOBs in Column B (the 2nd Column)

=VLOOKUP(HOURS!$A2,DOB!$A$2:$A$1296,2,0)

[mo4391]

Try copying this formula in column F of the 'Hours' sheet.

=VLOOKUP($A1,DOB!$A$1:$F$1296,6)

This is assuming the Employee IDs start on row 1 of both sheets and the last row with Employee IDs on the 'DOB' sheet is row 1296. You may have to adjust the formula accordingly if the start and end rows are different.

[Andreas_Zopo]

Thank you so much! both ways worked perfectly.

Now I have (another) related question

I was just requested to filter this data adding age as a parameter, but to a specific date.

I'm using the formula =VLOOKUP(DATEDIF($F2,TODAY(),"y"),Rates!$A$5:$C$80,2,1) that John gave me yesterday and it works PERFECTLY. But instead of TODAY (03/29/2016) I need the validation date for age to be 05/01/2015. I tried to put manually 05/01/2015 instead of TODAY() and of course it didn't work.

Thanks!

[daffodil11]

=VLOOKUP(DATEDIF($F2,Date(2015,5,1),"y"),Rates!$A$5:$C$80,2,1)

[Andreas_Zopo]

Thank you daffodil!

I have to say this is the most useful forum I've used in my life. Thanks a lot!