Searching for a formula which preoduce given result

ExcelMentee

Banned - Rules violations
Joined
Jan 11, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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.
8.PNG



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

Some videos you may like

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,568
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Jan 11, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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.

1611574206424.png
 
Last edited:

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,568
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Jan 11, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 22, 2012
Messages
1,568
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I Modified code but why start From Zero.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,568
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top