Search code examples
excelif-statementexcel-formulalogic

OR condition in Excel gives always TRUE

I have excel with 3 columns:

User Status Task
Mario done task 11
Ana cancelled task 12
Maria in work task 13
Selena in work task 14
Mario done task 15
Luca longrunner task 16
Luca done task 17
Selena in work task 18

I want to have result like this: in first column are people from column A: Mario, Ana, Selena, Luca In second column, next to the name, are their tasks, but only if the B column is "in work" or "longrunner"

I tried with:

=TEXTJOIN(", ",TRUE,UNIQUE(IF(OR(Sheet1!$B$2:Sheet1!$B$20="longrunner",Sheet1!$B$2:Sheet1!$B$20="in work"),IF(A2=Sheet1!$A$2:Sheet1!$A$20,Sheet1!$C$2:Sheet1!$C$20,""),"")))

but first if with OR command don't work well, it give me true all the time. When I place just one case of column B, without OR

=TEXTJOIN(", ",TRUE,UNIQUE(IF(Sheet1!$B$2:Sheet1!$B$20="in work",
IF(A2=Sheet1!$A$2:Sheet1!$A$20,Sheet1!$C$2:Sheet1!$C$20,""),"")))

it work well. What to do with first condition?

Solution

  • If i have understood correctly you might need to use this:

    enter image description here

    • Formula used in cell E10

    =LET(
a,FILTER(A2:C9,ISNUMBER(XMATCH(B2:B9,{"in work","longrunner"}))),
b,INDEX(a,,1),
c,INDEX(a,,3),
u,UNIQUE(b),
HSTACK(u,BYROW(u,LAMBDA(x,TEXTJOIN(", ",1,FILTER(c,x=b))))))

    Or, if you want to have blanks for those don't have in-work or longrunner then

    • Formula used in cell E2

    =LET(u,UNIQUE(A2:A9),
HSTACK(u,BYROW(u,LAMBDA(x,TEXTJOIN(", ",1,FILTER(C2:C9,(x=A2:A9)*(ISNUMBER(XMATCH(B2:B9,{"in work","longrunner"}))),""))))))