Forum Discussion
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.
=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?
4 Replies
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)Dharmendra_Bharwad Please check attached file
=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.
