SOLVED

Contributor

# Condition not working

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.

4 Replies
best response confirmed by Dharmendra_Bharwad (Contributor)
Solution

# Re: Condition not working

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

# Re: Condition not working

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

# Re: Condition not working

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