Condition not working

[Dharmendra_Bharwad]

Hi,

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.

[#2 · Accepted solution]

@Dharmendra_Bharwad 

=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?

[#3]

@OliverScheurich 

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

[#4]

@Dharmendra_Bharwad Please check attached file

[#5]

@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)