Excel Formula that Match the Year and Quarter then Paste the Result

Question

I have been using a Microsoft Excel formula that matches the Year and Quarter in Sheet1 then copies the required cell value and then paste that value into Sheet2 COl"B".

My formula is not dynamic it just works for year 2020 not for further years available in Sheet1 that are 2021 and 2020

I have tried to create something that works for my but could not. I have attached a Google Sheet please have look into it.

Your help will be highly appreciated.

https://docs.google.com/spreadsheets/d/1VgFn2Rpw3PAG_pBg1-YWfTexGuHHzzqiGBiUQDbpwLE/edit#gid=758402044

question from:https://stackoverflow.com/questions/65884626/excel-formula-that-match-the-year-and-quarter-then-paste-the-result

Answer 1

In C14 of Sheet2 try:

=IF(MOD(ROW(),3)=0,LOOKUP(2,1/((Sheet1!B$2:B$15=C$1+INT(COUNT(C$13:C13)/4))*(Sheet1!C$2:C$15=MOD(COUNT(C$13:C13),4)+1)),Sheet1!E$2:E$15),"")