Searching for a formula which preoduce given result

[ExcelMentee]

I have Two sheets name are Sheet1 and Sheet2. Sheet1 has different values in Quarters and Months.

Sheet2 have start date and end date which is populate months according to the dates.

Sheet1 ColB = Years and ColC = Months. I am looking for a formula which automatically assume it by matching the Sheet1 months and quarters to give Col"E" result in Sheet2 according to Sheet2 months.

formula will always pasted the result in Mid of the quarter as i did manually in sheet2 n below picture.


Sheet1 Col"B" is months and Col"C" is Quarter and Col"E" value will be copied.

Sheet2 where the result is pasted in mid of the quarter.

 

[maabadi]

Try this:
SHeet1

WorkBook2.xlsm
	A	B	C	D	E	F	G	H
1	KMPF	Years	QTR	KP1	KP2	KP3	KP4
2		2020	1		7500
3		2020	2		7600
4		2020	3		7700
5		2020	4		7800
6		2019	1		8000
7
Sheet1

Sheet2

WorkBook2.xlsm
	A	B	C	D	E	F	G
1		Year	2020		Month	Level1
2					1
3					2	7500
4					3
5					4
6					5	7600
7					6
8					7
9					8	7700
10					9
11					10
12					11	7800
13					12
14
Sheet2

Cell Formulas
Range	Formula
F2:F13	F2	=IFNA(INDEX(Sheet1!$E$2:$E$6,MATCH(1,((E2+1)/3=Sheet1!$C$2:$C$6)*($C$1=Sheet1!$B$2:$B$6),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ExcelMentee]

Thank you for the solution.

If situation is like below image where first 12 months are of 2020. and further are of 2021 then how formula will match further values instead of Cell C1 2020. Because your formula does work for 2020, If there are 2 year or more years and quarter available in Sheet1 then this formula will match and paste the result as well for 2021. AS it is working for 2020.

How to make it work for 2021 and further years.

I want to make it dynamic.

 

[maabadi]

Try this

Book1
	A	B	C	D	E	F	G	H
1	KMPF	Years	QTR	KP1	KP2	KP3	KP4
2		2020	1		7500
3		2020	2		7600
4		2020	3		7700
5		2020	4		7800
6		2021	1		8000
7		2021	2		8200
8		2021	3		8400
9		2021	4		8600
10		2022	1		8800
11		2022	2		9000
12		2022	3		9200
13		2022	4		9400
14
Sheet1

AND

Book1
	A	B	C	D	E	F	G	H
1		Year	2020		Month	Level1
2					1
3					2	7500
4					3
5					4
6					5	7600
7					6
8					7
9					8	7700
10					9
11					10
12					11	7800
13					12
14					1
15					2	8000
16					3
17					4
18					5	8200
19					6
20					7
21					8	8400
22					9
23					10
24					11	8600
25					12
26					1
27					2	8800
28					3
29					4
30					5	9000
31					6
32					7
33					8	9200
34					9
35					10
36					11	9400
37					12
38
Sheet2

Cell Formulas
Range	Formula
F2:F37	F2	=IFNA(INDEX(Sheet1!$E$2:$E$13,MATCH(1,((E2+1)/3=Sheet1!$C$2:$C$13)*(($C$1+INT(ROWS($E$2:E2)/12))=Sheet1!$B$2:$B$13),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ExcelMentee]

Thank you once again for the but the pattern that will be followed for the months will be as i posted a picture like this below
First 12 are 2020 2nd are 2021 months will not start from again 1 to 12

0​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

17​

18​

19​

20​

21​

22​

23​

24​

24​

 

[maabadi]

I Modified code but why start From Zero.

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F	G
1		Year	2020		Month	Level1
2					1
3					2	7500
4					3
5					4
6					5	7600
7					6
8					7
9					8	7700
10					9
11					10
12					11	7800
13					12
14					13
15					14	8000
16					15
17					16
18					17	8200
19					18
20					19
21					20	8400
22					21
23					22
24					23	8600
25					24
26					25
27					26	8800
28					27
29					28
30					29	9000
31					30
32					31
33					32	9200
34					33
35					34
36					35	9400
37					36
38
Sheet2

Cell Formulas
Range	Formula
F2:F37	F2	=IFNA(INDEX(Sheet1!$E$2:$E$13,MATCH(1,((MOD(E2,12)+1)/3=Sheet1!$C$2:$C$13)*(($C$1+INT(E2/12))=Sheet1!$B$2:$B$13),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ExcelMentee]

thank you very very much.

 

[maabadi]

You're welcome & Thanks For Feedback.