Forum Discussion

BenjoBenjo's avatar
BenjoBenjo
Copper Contributor
Oct 25, 2023
Solved

IF AND OR

Hi everyone, Could someone pls help me on using IF AND OR functions. I wanted do a kinda XLoopup but more of a IF with multiple criteria. I have a leave taken and ended in a sheet called COMP Leave ...
excel
  • SanthoshKunder's avatar
    SanthoshKunder
    Oct 26, 2023

    BenjoBenjo - Not sure why it's not working for you. I copied your final formula and it works for me.

     

    Regarding --, we must use --. It's simple. First hyphen(-) negates the TRUE and the second hyphen reverse the negation(Negative multiplied by Negative is a positive number).

     

BenjoBenjo's avatar
BenjoBenjo
Copper Contributor
Oct 26, 2023

SanthoshKunder Hi Santosh, Your formula was a great help but the text "LWoP" applies to all the employees from the date 10 July till 31 July. Seems like its not picking particular employee "E0144". Would really appreciate your help. My final formula is 

IF(SUM(--(L$7>=COMP_Leave[[LeaveStartDate]:[LeaveStartDate]])*(L$7<=COMP_Leave[[LeaveEndDate]:[LeaveEndDate]])*(COMP_Leave[[EntitlementType]:[EntitlementType]]="UNPAID LEAVE")*(COMP_Leave[[EmployeeID]:[EmployeeID]]="E0144"))=1,"LWoP",5)

 

Also, does a number of -- makes a difference? When should we put - and when to put -- 

SanthoshKunder's avatar
SanthoshKunder
Iron Contributor
Oct 26, 2023

BenjoBenjo - Not sure why it's not working for you. I copied your final formula and it works for me.

 

Regarding --, we must use --. It's simple. First hyphen(-) negates the TRUE and the second hyphen reverse the negation(Negative multiplied by Negative is a positive number).

 

Resources