vlookup with two data points.

[DataGuy]

Ok...I am not sure how to set up this formula.

Sheet 1: Column A:Employee ID # B:Effective Dates
Sheet 2: Column A:Employee ID # B:Effective Dates C:Change Reasons

Example Data:
Sheet 1:

Employee ID Effective Date Change Reasons
11734 3/23/2009
11734 12/16/2008
11734 12/1/2008
20045 6/5/2008

Sheet 2:

Employee ID Effective Date Change Reason
11734 3/23/2009 New Hire
11734 12/1/2008 Manager Change

I need to be able to get the 'Change Reasons' in Sheet 1 if the Employee ID
and Effective Dates are the same on both sheets.

Normal, I would do a vlookup, but since two items have to true, I am not
sure how to pull the data. Any advice is much appreciated. Thanks.

 

[KC]

in Sheet1 colum C2 use this formula and drag it to all other below cells!

=IF(VLOOKUP(A2,Sheet2!$A$2:$C$3,2,FALSE)=B2,VLOOKUP(A2,Sheet2!$A$2:$C$3,3,FALSE),"")

-kc
*Click YES if this works

 

[DataGuy]

Hi KC,

The formula did not work.

I copied the formula as is and it only pasted the 'New Hire' reason in C2 on
sheet1. It did not paste the 'Manager Change' in C4 next to the effective
date of 12/1/08 in sheet1. I got a #N/A in C5, which I expected since 20045
employee ID number does not exist in Sheet2.

Any other suggestions?
Thanks, Casey

 

[joemeshuggah]

maybe try adding a column that concatenates the employee id and effective
date, and then base the vlookup off of that column?