Condition not working




I have attached the excel file wherein I want to have output as "1" in cell B5 in worksheet True. However, it is deriving the output as "2".


The condition is that whenever the cell in row 2 of Demo sheet is updated with "ACTUAL", the value in cell B2 of True sheet should update as "1". For example, if the cell I2 & J2 in Demo sheet are updated as "ACTUAL", the value in cell B5 of True sheet shall result into "1" upto the month of Sep-21 (row 3) and thereon "2". 


I am open with any other formula.

4 Replies
best response confirmed by Dharmendra_Bharwad (Contributor)


=IF(OFFSET(Demo!$B$3,-1,MATCH('True'!B$3,Demo!$B$3:$M$3,0)-1,,)="ACTUAL ",1,2)


Is this the formula you are looking for?


Thank you very much. This is exactly what I needed.

@Dharmendra_Bharwad Please check attached file

@Dharmendra_Bharwad Since you tagged you post with Office365, you Excel version probably supports XLOOKUP and dynamic arrays.


If you insist on 1 for "ACTUAL " and 2 if not "ACTUAL ", enter this in B5:

=IF(XLOOKUP(B3:M3,Demo!B3:M3,Demo!B2:M2)="ACTUAL ",1,2)

If you could accept 1's and 0's, try this:

=--(XLOOKUP(B3:M3,Demo!B3:M3,Demo!B2:M2)="ACTUAL ")

Or perhaps you'll like this one as well, assuming that the order of the columns is the same in the True sheet as in the Demo sheet:

=SWITCH(Demo!B2:M2,"ACTUAL ",1,"OLD",2)