# Searching for a formula which preoduce given result

#### ExcelMentee

##### Banned - Rules violations
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.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

##### Well-known Member
Try this:
SHeet1
WorkBook2.xlsm
ABCDEFGH
1KMPFYearsQTRKP1KP2KP3KP4
2202017500
3202027600
4202037700
5202047800
6201918000
7
Sheet1

Sheet2
WorkBook2.xlsm
ABCDEFG
1Year2020MonthLevel1
21
327500
43
54
657600
76
87
987700
109
1110
12117800
1312
14
Sheet2
Cell Formulas
RangeFormula
F2:F13F2=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

##### Banned - Rules violations
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.

Last edited:

##### Well-known Member
Try this
Book1
ABCDEFGH
1KMPFYearsQTRKP1KP2KP3KP4
2202017500
3202027600
4202037700
5202047800
6202118000
7202128200
8202138400
9202148600
10202218800
11202229000
12202239200
13202249400
14
Sheet1

AND

Book1
ABCDEFGH
1Year2020MonthLevel1
21
327500
43
54
657600
76
87
987700
109
1110
12117800
1312
141
1528000
163
174
1858200
196
207
2188400
229
2310
24118600
2512
261
2728800
283
294
3059000
316
327
3389200
349
3510
36119400
3712
38
Sheet2
Cell Formulas
RangeFormula
F2:F37F2=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

##### Banned - Rules violations

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​

##### Well-known Member
I Modified code but why start From Zero.

##### Well-known Member

Try this:
Book1
ABCDEFG
1Year2020MonthLevel1
21
327500
43
54
657600
76
87
987700
109
1110
12117800
1312
1413
15148000
1615
1716
18178200
1918
2019
21208400
2221
2322
24238600
2524
2625
27268800
2827
2928
30299000
3130
3231
33329200
3433
3534
36359400
3736
38
Sheet2
Cell Formulas
RangeFormula
F2:F37F2=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

##### Banned - Rules violations
thank you very very much.

##### Well-known Member
You're welcome & Thanks For Feedback.

Replies
4
Views
122
Replies
1
Views
32
Replies
7
Views
401
Replies
10
Views
524
Replies
2
Views
105

1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back