How to do an if statement-CodePudding

this question is linked to

if formula in excel that checks a condition and if its verified it proceeds to use another excel formula

CodePudding user response：

You could try the following as per your Excel Versions

• Formula used in cell B2

=IF(OR(TEXTBEFORE(A2&" "," ")={"Pier","Gian"}),A2,TEXTBEFORE(A2&" "," "))

Or, in cell C2

=IF(OR(LEFT(A2&" ",FIND(" ",A2&" ")-1)={"Pier","Gian"}),A2,LEFT(A2&" ",FIND(" ",A2&" ")-1))

Just adding the use of LET() which makes it simpler,

• Formula used in cell B2

=LET(x,TEXTBEFORE(A2&" "," "),IF(OR(x={"Pier","Gian"}),A2,x))

Or, Formula used in cell C2

=LET(x,LEFT(A2&" ",FIND(" ",A2&" ")-1),IF(OR(x={"Pier","Gian"}),A2,x))

Using MAP() to Spill as one dynamic array formula but the logic remains same.

• Formula used in cell D2

=MAP(A2:A6,LAMBDA(m,
LET(x,TEXTBEFORE(m&" "," "),
IF(OR(x={"Pier","Gian"}),m,x))))

CodePudding user response：

you have to use the AND(...) and OR(..) for chaining logical conditions.