Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulavlookupifs

How to coerce text using IFS function in Google Sheets?

I have a text in the cell A3 =SCHOOL

I have built a formula in the cell B3

=IFS(A3= "SCHOOL", "ST. XAVIER", A3="COLLEGE", "OXFORD")

But the problem is that, the error is coming that 'SCHOOL' is a text and cannot be coerced to a number. I want to change the values conditionally. Please help me.

B3 should be OXFORD when the value is in A3 as COLLEGE.

Solution

  • you can do:

    =IF(A3="SCHOOL",  "ST. XAVIER", 
 IF(A3="COLLEGE", "OXFORD", ))

    or:

    =SWITCH(A3, "SCHOOL", "ST. XAVIER", "COLLEGE", "OXFORD")

    enter image description here

    but you can always add (nest) more IF statements:

    =IF(A3="SCHOOL",  "ST. XAVIER", 
 IF(A3="COLLEGE", "OXFORD", 
 IF(A3="xxxxxxx", "yyy", )))

    =IF(A3="SCHOOL",  "ST. XAVIER", 
 IF(A3="COLLEGE", "OXFORD", 
 IF(A3="xxxxxxx", "yyy", 
 IF(A3="fkhfgkh", "gghjv", ))))

    next you can use:

    =XLOOKUP(A3, {"SCHOOL", "COLLEGE"}, {"ST. XAVIER", "OXFORD"}, )

    enter image description here

    or:

    =VLOOKUP(A3, {"SCHOOL",  "ST. XAVIER"; 
              "COLLEGE", "OXFORD"}, 2, )

    enter image description here