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

carlosrojaspdx

New Member
Joined
Jul 8, 2009
Messages
13
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 NameStatus ChangeFTEEmployee NamePayroll Start Date<== Lookup criteria
John Doe 5/5/20141.00John Doe8/1/2014
Hillary Clinton 7/10/20141.00
Bernie Sanders 9/25/20140.50goal is to get ==>0.50
Marco Rubio6/12/20140.80from cell C6
John Doe 7/15/20140.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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

carlosrojaspdx

New Member
Joined
Jul 8, 2009
Messages
13
@ 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

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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

Board Regular
Joined
Apr 2, 2009
Messages
175

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

Board Regular
Joined
Apr 2, 2009
Messages
175
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

New Member
Joined
Jul 8, 2009
Messages
13

ADVERTISEMENT

Thank you for your help. I get this result

"#VALUE!"

<tbody>
</tbody>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Jul 8, 2009
Messages
13
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,499
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,531
Messages
5,602,202
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top