Jan 13 2023 10:50 AM
Cells A2:C2: are populated with text. How can I return a value to D2, that is determined by different combinations of A2:C2? Based only on whether or not A2:C2 values contain text or are empty. D2 would be a different value than any of the A2:E2 values.
Example:
A2= Cell Number
B2= Direct Number
C2= email
All formatted as text.
If A,B, and C all contain text, return a value of x.
If only A and C contain text return a value of y.
If only C is populated with text return a value of z.
Jan 13 2023 11:01 AM
=IF(AND(ISTEXT(A2),ISTEXT(B2),ISTEXT(C2)),"x",IF(AND(ISTEXT(A2),NOT(ISTEXT(B2)),ISTEXT(C2)),"y",IF(AND(NOT(ISTEXT(A2)),NOT(ISTEXT(B2)),ISTEXT(C2)),"z","")))
You can try a nested IF formula.
Jan 13 2023 11:07 AM
Jan 13 2023 11:16 AM
As variant
=SWITCH(SUM(ISTEXT(A2:C2)*{1,10,20}), 31, "x", 21, "y", 20, "z", "not defined")
Jan 13 2023 11:40 AM
=MAP(A2:A7,B2:B7,C2:C7,LAMBDA(colA,colB,colC,IFS(AND(ISTEXT(colA),ISTEXT(colB),ISTEXT(colC)),"x",AND(ISTEXT(colA),NOT(ISTEXT(colB)),ISTEXT(colC)),"y",AND(NOT(ISTEXT(colA)),NOT(ISTEXT(colB)),ISTEXT(colC)),"z",TRUE,"")))
Maybe you can apply this formula with Office 365 or Excel for the web alternatively.