Calculate value based on date = date in another cell

[AMP128]

Hi all,
How do I calculate vested shares, in a particular month, based on the vesting date?

For example, I am trying to solve for Cells I2 and N2, the correct answer will be 25,000, however I don't know how to set up the formula to calculate based on the date in row 1.

D2:G2 are the vesting dates.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1

[/TD]
[TD]Name[/TD]
[TD]Amount[/TD]
[TD]Vesting Terms[/TD]
[TD]1st[/TD]
[TD]2nd[/TD]
[TD]3rd[/TD]
[TD]4th[/TD]
[TD][/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Employee[/TD]
[TD]100,000[/TD]
[TD]25%[/TD]
[TD]Jan-17[/TD]
[TD]Jun-17[/TD]
[TD]Jun-18[/TD]
[TD]Jun-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[duggie33]

Hi AMP128,

I made some assumptions since it was not completely clear to me. I am assuming that in Jan-2017 Employee is vested at 25%, in Jun-2017 vested at 50%, Jun-2018 vested at 75%, and Jun-2019 vested at 100%. If that is the case, one possible solution is to use the Match function.

The equation to put in cell I2 would be: =MATCH(J1,$E$2:$H$2,1)*$D$2*$C$2

You can copy that equation to the cells to the right of it.

Hope that helps,

Doug

 

[duggie33]

Sorry my ranges of off by one because I copied your data from above to Excel and had an extra column so the updated formula is: =MATCH(I1,$D$2:$G$2,1)*$C$2*$B$2

Doug

 

[AMP128]

Thank you!