Forum Discussion

Anubhav_Saxena's avatar
Anubhav_Saxena
Copper Contributor
Dec 18, 2022

Check condition repeatedly

I have two sheets named "Avg" and "Data" in one workbook. B column of Avg contains month and year. and B column of Data contains date in dd-mmm-yyyy format. I have to write a formula for C column of Data to undergo an operation wherever corresponding date in B column of Data matches with month and year in B column of Avg. For each entry in B Column of Data, entire range in B column of Avg has to be checked each time. How to do this pls suggest? 

 

 

Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Dec 19, 2022
    Attach a sample file and show your input and desired output.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 19, 2022
    if they are corresponding columns you should be able to use:
    =IF(EOMONTH(B2,0)=EOMONTH(Avg!B2,0), [do calculation], "")
    and fill down (I don't believe EOMONTH() works well with dynamic arrays)
    alternatively you could use:
    =IF(DATE(YEAR(B1:B100),MONTH(B1:B100),1)=Avg!B1:B100, [do calculation], "")
    but that assumes the month year is truly month year (i.e. day is always 1) and change B1:B100 to the appropriate rows you need to check. Alternatively i highly recommend changing the ranges to be Formatted as a Table and then use the Table references.

Resources