Finding Effective Salary

[UMBiii]

I have a sheet with employees, their salaries and the effective dates of the salaries.

On another sheet within the workbook, I have pay period end dates and would like to be able to pull the relevant effective salary at the time into the corresponding row's pay period. e.g.

John Smith Salary effective 1/1/2012 - $50,000
John Smith Salary effective 2/12/2012 - $55,000

Employee Name	Salary	Pay Period End Date
John Smith	?	1/6/2012
John Smith	?	1/20/2012
John Smith	?	2/3/2012
John Smith	?	2/17/2012

If the Pay period end date is greater than the effective date, I would like the method to pull the new effective date. I have attached a larger excel file with another example. Thanks for everyone's assistance in advance!

Finding effective salary example.xlsx

[sktneer]

Try this...

In B2

=IFERROR(INDEX('Employee Salary History'!$B$2:$B$12,MATCH(C2,'Employee Salary History'!$C$2:$C$12)),"")

and copy down.

Is this what you are trying to achieve?

[UMBiii]

Not quite, it doesn't look like this formula pulls the correct matching relevant information. Thank you for your assistance though.

I have exhausted a lot of attempts with the index, match, address and indirect functions. I think I will need a macro to utilize more powerful logic functions. Thanks again!

[newdoverman]

The sample is really very small, maybe too small but this is what I came up with:

[newdoverman]

@sktneer

I came up with the same solution that you did. There has to be something that we are not being told for it not to work.

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[sktneer]

@ newdoverman
I think he needs to show the desired output.

[samba_ravi]

=MAX(INDEX(($A2='Employee Salary History'!$A$2:$A$12)*($C2>='Employee Salary History'!$C$2:$C$12)*('Employee Salary History'!$B$2:$B$12),0))
Try this

[sktneer]

Or this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.

=IFERROR(INDEX('Employee Salary History'!$B$2:$B$12,MATCH(C2,IF('Employee Salary History'!$A$2:$A$12=A2,'Employee Salary History'!$C$2:$C$12))),"")

Is this what you are trying to achieve?

[UMBiii]

Thank you Sktneer, my apologies in not being clearer. I should have attached the desired output of my problem. Thank you again for your assistance!

And just wanted to thank everyone for their assistance with this!

[sktneer]

@ UMBiii
You're welcome. Thanks for the feedback.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

[newdoverman]

@sktneer

A desired output would certainly help eliminate the guessing in this. To me, your last upload is a reasonable interpretation and solution of the problem.

[sktneer]

@ newdoverman
I hoped so and OP has also responded positively.