Index/match = 2 criteria, one Employee name, other is the closest date for status change

[carlosrojaspdx]

Please help. I've been searching and trying different index/match formulas, but I can not get this right. I need to find the closest date of status change to the payroll date.

EE Name	Status Change	FTE		Employee Name	Payroll Start Date	<== Lookup criteria
John Doe	5/5/2014	1.00		John Doe	8/1/2014
Hillary Clinton	7/10/2014	1.00
Bernie Sanders	9/25/2014	0.50		goal is to get ==>	0.50
Marco Rubio	6/12/2014	0.80		from cell C6
John Doe	7/15/2014	0.50
Lookup values: 1 = Employee Name, 2 is payroll date:

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

If my payroll start date is 8/1/2014, how to do I write a formula that will give me the most current FTE based on the date that is the closest to the payroll start date (8/1/2014), and will continue to give me last FTE change for all the rest of the employees?

Hope that makes sense. Thank you in advance for your help.

 

[Aladin Akyurek]

I did use the CTR-shift and then enter and the {} are in the formula. I will be glad to send you the file. Thank you so much for your help, I've tried several formulas, but no luck yet.

You could repost. Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit.

 

[carlosrojaspdx]

Thank you, that did the trick. Much appreciate it

 

[Aladin Akyurek]

They are formatted as date

Try to run an ISNUMBER test. For a true date, you'll get TRUE...

 

[jtakw]

You're welcome.

 

[carlosrojaspdx]

Thank you. I encounter a problem: Dates don't match closely, here is an example:

Employee Name	Actual date of FTE Change	Actual FTE		Payroll Period Start Date	FTE formula shows
John Doe	8/12/2013	0.65		Up to 11/30/2013	0.65
John Doe	3/21/2014	0.80		Starting 12/1/2013	0.80
John Doe	11/17/2014	0.90		Starting 8/1/2014	0.90
John Doe	9/28/2015	0.50		Starting 5/1/2015	0.50

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

In the first example, 0.65 FTE should show for payroll start date up to 3/20/2014, instead it stops on 11/30/2013, and so on.

 

[Aladin Akyurek]

Thank you. I encounter a problem: Dates don't match closely, here is an example:

Employee Name	Actual date of FTE Change	Actual FTE		Payroll Period Start Date	FTE formula shows
John Doe	8/12/2013	0.65		Up to 11/30/2013	0.65
John Doe	3/21/2014	0.80		Starting 12/1/2013	0.80
John Doe	11/17/2014	0.90		Starting 8/1/2014	0.90
John Doe	9/28/2015	0.50		Starting 5/1/2015	0.50

<tbody>
</tbody>

In the first example, 0.65 FTE should show for payroll start date up to 3/20/2014, instead it stops on 11/30/2013, and so on.

What should be the values in column E instead?

 

[carlosrojaspdx]

Our payroll is semi-monthly. i.e. in January of 2013, payroll period dates are 1/1/2013 to 1/15/2013, and 1/16/2013 to 1/31/2013. The payroll start dates then are: 1/1/2013 and 1/16/2013.

in the actual data above, the first rows in column E with payroll start date from 8/12/2013 to 3/21/2014 should be 0.65, but the 0.65 only appears in payroll with start date up to 11/30/2013. Then it changes to 0.80 starting with pyrl start date of 12/1/2013. But it should not change until 3/16/2014.

Same for the next rows. Hope that makes sense.

Thank you so much. Carlos

 

[carlosrojaspdx]

These 2 formulas give the correct nearest date, but don't know how to incorporate them into the index/match formula to also search for the 2nd criteria:

=SMALL(B2:B5,COUNTIF(B2:B5,"="&VALUE($AX$36)))

<tbody>
</tbody>

where AX36 is the payroll start date

=MAX((B2:B5<=AX36)*B2:B5)

Any help is greatly appreciate it.

 

[Aladin Akyurek]

These 2 formulas give the correct nearest date, but don't know how to incorporate them into the index/match formula to also search for the 2nd criteria:

=SMALL(B2:B5,COUNTIF(B2:B5,"="&VALUE($AX$36)))

<tbody>
</tbody>

where AX36 is the payroll start date

=MAX((B2:B5<=AX36)*B2:B5)

Any help is greatly appreciate it.

Row\Col	A​	B​	C​	D​
1​	Employee Name	Actual date of FTE Change	Actual FTE	Payroll Period Start Date
2​	John Doe	8/12/2013​	0.65​	Up to 11/30/2013
3​	John Doe	3/21/2014​	0.8​	Starting 12/1/2013
4​	John Doe	11/17/2014​	0.9​	Starting 8/1/2014
5​	John Doe	9/28/2015​	0.5​	Starting 5/1/2015

This is what you posted, exluding the last column where an apparently non-working formula delivers.

Two observations:

1) Desired values are still missing.
2) The field Payroll Period Start Date contains hard-to-use values if they are needed in producing the desired values.

 

[carlosrojaspdx]

Here is what is happening with the formulas in C2:C15

{=INDEX($I$2:$I$6,MATCH(MIN(IF(A2=$G$2:$G$6,ABS($H$2:$H$6-B2))),IF(A2=$G$2:$G$6,ABS($H$2:$H$6-B2)),0))} 1 A B C D E F G H I 2 EE Name John Doe John Doe John Doe John Doe John Doe John Doe John Doe John Doe John Doe John Doe John Doe John Doe John Doe John Doe <colgroup><col></colgroup><tbody> </tbody> Pay Period Start 9/16/2013 10/1/2013 12/1/2013 12/16/2013 4/1/2014 4/16/2014 8/1/2014 8/16/2014 12/1/2014 12/16/2014 5/1/2015 5/16/2015 10/1/2015 10/16/2015 <colgroup><col></colgroup><tbody> </tbody> Index-Match Formula results 0.65 0.65 0.80 0.80 0.80 0.80 0.90 0.90 0.90 0.90 0.50 0.50 0.50 0.50 <colgroup><col></colgroup><tbody> </tbody> What should be 0.65 0.65 0.65 0.65 0.80 0.80 0.80 0.80 0.90 0.90 0.90 0.90 0.50 0.50 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody> Notes FTE of 0.65 since 8/12/2013 On 3/21/2014 FTE changed to 0.80 On 11/17/2014 FTE changed to 0.90 On 9/28/2015 FTE changed to 0.50 <colgroup><col></colgroup><tbody> </tbody> EE Name John Doe John Doe John Doe John Doe John Doe <colgroup><col></colgroup><tbody> </tbody> FTE Change Date 5/21/2013 8/12/2013 3/21/2014 11/17/2014 9/28/2015 <colgroup><col></colgroup><tbody> </tbody> FTE 0.80 0.65 0.80 0.90 0.50 <colgroup><col></colgroup><tbody> </tbody> <tbody> </tbody> ​

<tbody>
</tbody>

Verified that all dates are formatted as dates, use date values to begin with and then formatted them as dates
Tried to match the table with rows, but it paste every thing into 1 cell. Thank you for your help.