Pull month and year index for each day of that month and year

[askall1000]

Hello, would you help me about formula to pull index base on month and year to a list which contains days, month and year; so every day for that month and year have same index number. Thank you very much in advance.

Book4
	B	C
2	Date (day/mm/year format)	Indeks
3	01.01.2004	1,54
4	01.02.2004	1,64
5
6	Date (day/mm/year format)	Indeks
7	01.01.2004	1,54
8	02.01.2004	1,54
9	03.01.2004	1,54
10	04.01.2004	1,54
11	05.01.2004	1,54
12	06.01.2004	1,54
13	07.01.2004	1,54
14	08.02.2004	1,64
15	09.02.2004	1,64
16	10.02.2004	1,64
17	11.02.2004	1,64
18	12.02.2004	1,64
19	13.02.2004	1,64
20	14.02.2004	1,64
Sheet1

 

[askall1000]

I find this formula from internet and adopted to my list and it works who might be need in the future SUMPRODUCT((YEAR(D24)=YEAR(100!$B$2:$B$228))*(MONTH(D24)=MONTH(100!$B$2:$B$228));(100!$D$2:$D$228)), but if there is alternative please share. Thank you.

 

[Kaper]

The formula you published do not reflect your sample data .

I've prepared an answer assuming that you look for formula in C7 (and copied down) in your sample . Such formula shall do:

=VLOOKUP(EOMONTH(B7,-1)+1,$B$3:$C$4,2,0)

On the other hand, if you would look for C3 (and copy down) formula then it would be:

=INDEX($C$7:$C$20,MATCH(B3,$B$7:$B$20)+(COUNTIF($B$7:$B$20,B3)=0))

 

[askall1000]

Hi Kaper, yes you are right, apologize for misunderstanding. My formula is base on index on sheet 100, I shared sample in one sheet then with the solution from forum, i can revize it according to my worksheet. The shared solution base on my worksheet. Also Thank you very much for your replies, much appreciated.