Check condition repeatedly

Copper Contributor

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? 

 

 

2 Replies
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.
Attach a sample file and show your input and desired output.