| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with two data points.
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup with two data points.
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,VLOOKU P(A2,Sheet2!$A$2:$C$3,3,FALSE),"") -kc *Click YES if this works "DataGuy" wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup with two data points.
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 "KC" wrote: 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,VLOOKU P(A2,Sheet2!$A$2:$C$3,3,FALSE),"") -kc *Click YES if this works "DataGuy" wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup with two data points.
maybe try adding a column that concatenates the employee id and effective
date, and then base the vlookup off of that column? "DataGuy" wrote: 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 "KC" wrote: 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,VLOOKU P(A2,Sheet2!$A$2:$C$3,3,FALSE),"") -kc *Click YES if this works "DataGuy" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How select all data points/data labels at once (to format)? | Charts and Charting in Excel | |||
| connect line to data points with missing data in between | Charts and Charting in Excel | |||
| no data printed in chart with more than 2709 data points | Charts and Charting in Excel | |||
| Formatting data series - line between certain data points only | Charts and Charting in Excel | |||
| excel 2007, how to select a data point and cycle through data points | Charts and Charting in Excel | |||
Linear Mode
