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.

 

[cooper645]

Are another thread here that may put you on the right track

http://www.mrexcel.com/forum/excel-questions/326266-how-find-closest-date.html

 

[carlosrojaspdx]

@ cooper645, thank you, yes there are several, I've read so many, but didn't find one dealing with the exact same issues. I found close ones and tried many possible formulas, but none work

 

[cooper645]

Hopefully someone will be along soon to help, I'm away with work at the moment and only have my phone which is limiting. I will check up on this when I'm back next month hopefully and see if it has been solved or I can help.

good luck in the interim

 

[mubashiraziz]

ADVERTISEMENT

Try this

=INDEX($C$2:$C$6,MATCH(MIN(IF(E2=A2:A6,ABS(B2:B6-F2))),IF(E2=A2:A6,ABS(B2:B6-F2)),0))

 

[mubashiraziz]

Try this
Entered with Ctrl+shift+Enter
{=INDEX($C$2:$C$6,MATCH(MIN(IF(E2=A2:A6,ABS(B2:B6-F2))),IF(E2=A2:A6,ABS(B2:B6-F2)),0))}

 

[carlosrojaspdx]

ADVERTISEMENT

Thank you for your help. I get this result

"#VALUE!"

<tbody>
</tbody>

 

[Aladin Akyurek]

Thank you for your help. I get this result

"#VALUE!"

<tbody>
</tbody>

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel puts a pair of { and } around the formula in recognition.

 

[carlosrojaspdx]

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.

 

[jtakw]

Hi,

Also make sure your dates in Column "Status Change" and Payroll Start Date are formatted as Date.
It may just be the way it's displayed, but your Status Change column dates are left aligned, which normally indicates they are Text.