Matching Date & Value of another cell

[Slumbery]

Hello folks, really hope you guys can help me out here.

I want to set up a formula so that on the specified date, the formula matches the value in the cell next to it and inputs it in another value.
For instance, here is an example of what I have.

In column F I have unit numbers and they vary, some are in numerical order and some aren't. In column G, I have dated in yearly order. For instance 2016 - 2019 (long date). What I want to do is have excel check the date and based off the current date, it checks the period its in and the corresponding unit number and input it in another cell. It looks like this.

Colum F: Unit Dates
1 October 15, 2015
2 October 15, 2016
3 October 15, 2017
4 October 15, 2018
5 October 15, 2019

Since we're still in 2017, there should be 3 units. Now the formula should know this and have this value in another input. Once it hits Oct 15, 2018, that value should changed to 4 and so on.

I hope it makes sense.

 

[admiral100]

Hi,

Try this:

I2 =IFERROR(INDEX(F2:F6,MATCH(G2,IF(G2:G6<=TODAY(),G2:G6),0)),"")
J2 =SUMPRODUCT(--(G2:G6<=TODAY()))

[TABLE="width: 468"]
<colgroup><col span="2"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Unit[/TD]
[TD]Dates[/TD]
[TD][/TD]
[TD]Find Unit[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]15 October 2015[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]15 October 2016[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]15 October 2017[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]15 October 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]15 October 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]