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]

What is the exact range Paydate refers to?

Paydate refers to the actual paydate of the company employees (Pay period end + 7 days). It's every 2 weeks.

 

[Aladin Akyurek]

Paydate refers to the actual paydate of the company employees (Pay period end + 7 days). It's every 2 weeks.

Examples of 'exact range'...

A2:B2

Sheet1!A2:A10

Your...

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

would suggest that Paydate is a table occupying a range...

 

[laxcat73]

Examples of 'exact range'...

A2:B2

Sheet1!A2:A10

Your...

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

would suggest that Paydate is a table occupying a range...

Correct.

A1:A111 Dates only, every 2 weeks.

 

[Aladin Akyurek]

Correct.

A1:A111 Dates only, every 2 weeks.

Does the following...

=INDEX(Paydate,MATCH(E2,Paydate,1)+(LOOKUP(E2,Paydate) < E2))

help where E2 houses a date of interest which you indicate I guess as [Date]?

 

[laxcat73]

Thanks for the assistance but I don't think I'm coming across in a cohesive manner. I'll figure something out.

 

[Aladin Akyurek]

Thanks for the assistance but I don't think I'm coming across in a cohesive manner. I'll figure something out.

You could try to create a small sample and specify a desired result in terms of that sample.