Help With Tricky Formula?

[laxcat73]

Background:

I am creating a spreadsheet for bonus payments. These bonus payments are structured based on experience of the referred candidate. I am trying to creat a formula that will give me a result of the Pay Period for which that bonus is due. I was thinking something along the lines of rounding a date up to the paydate (pay period end + 7 days) which is every 2 weeks.

I cannot find any formula to help so I'm guessing it's a combination of simpler formulas given certain conditions. This is my current formula:

=VLOOKUP(IF(M4="",I4,IF(S4="",O4,IF(Y4="",U4,IF(AE4="",AA4,"")))),Paydate,1,TRUE)

However, using the VLOOKUP True condition, it simply rounds to the nearest paydate, not the succeeding one. Is there anything that could have that round up to the next?

Thank you in advance.

 

[laxcat73]

Nevermind the nested if statements, it's simply finding the most recent structured payment that has not been met.

 

[laxcat73]

Help please!

 

[AMAS]

Could you let us know what is each column?

AMAS

 

[laxcat73]

Could you let us know what is each column?

AMAS

Ah, in the formula above

=VLOOKUP(IF(M4="",I4,IF(S4="",O4,IF(Y4="",U4,IF(AE4="",AA4,"")))),Paydate,1,TRUE)

M4, S4, U4, AA4 are the 4 scheduled payouts, chronologically (Date of hire, +60 days, +180 days, +1 year). It's a tiered payment schedule based on how long the referred employee stays with the organization. The nested IF statments are currently setup so it displays the most recent unpaid liability without the lookup function.

Also, in the "Paydate" Named range is a list of the paydates - every 2 weeks according to payroll.

 

[AMAS]

So I4, O4, U4 and AA4 would contain the figure that you want to use in the VLookup. Correct?

And column 1 in the Paydate is what you want to retrieve.

AMAS

 

[laxcat73]

So I4, O4, U4 and AA4 would contain the figure that you want to use in the VLookup. Correct?

And column 1 in the Paydate is what you want to retrieve.

AMAS

Correct. The formula I use now using TRUE in the lookup brings up the nearest paydate in retreival. I would like to get the next paydate rather than closest.

 

[AMAS]

Is this what you are looking for?

=VLOOKUP(IF(M4="",O4,IF(S4="",U4,IF(Y4="",AA4,IF(AE4="","Don't know","")))),Paydate,1,TRUE)

AMAS

 

[laxcat73]

Sorry, Amas, I think you are approaching my question in a different manner than I am asking.

You need to ignore the nested if statement. Formula again is as follows, edited slightly to simplify my point

=VLOOKUP([Date],Paydate,1,TRUE)

Is there any way for the lookup function to round UP to the NEXT paydate in the "Paydate" named range? Currently, it just rounds to the CLOSEST paydate including one that has already passed. That is useless to me.

 

[Aladin Akyurek]

Sorry, Amas, I think you are approaching my question in a different manner than I am asking.

You need to ignore the nested if statement. Formula again is as follows, edited slightly to simplify my point

=VLOOKUP([Date],Paydate,1,TRUE)

Is there any way for the lookup function to round UP to the NEXT paydate in the "Paydate" named range? Currently, it just rounds to the CLOSEST paydate including one that has already passed. That is useless to me.

What is the exact range Paydate refers to?