Forum Discussion

Roxana_Menendez's avatar
Roxana_Menendez
Copper Contributor
Aug 20, 2021
Solved

Use multiple IF functions in one cell without nesting

Hi All, 

 

I don't even know if this is possible but here's what I'm trying to do:

 

I have an excel and in cells F, I, M, N and O have a formula that either puts a value or a "not eligible" phrase. What I'm trying to do is put in cell Q the description why it is "not eligible" and it can have multiple results, so a nested IF would not work (I think).

 

So "ideally" the formula should be something like this:

=IF(F7="Not eligible", "QA", ""),IF(I7="Not eligible", "Tardies", ""),IF(M7="Not eligible", "Break & Lunch", ""),IF(N7="Not eligible", "Personal", ""),IF(O7="Not eligible", "Absences", "")

 

i.e: if in cell F, M, and O it finds the phrase "not eligible" the result should be: "QA, Break & Lunch, Absences"

 

Of course the formula it is not working and it's giving me a "Formula parse error"

 

So I guess I'm trying to look for some sort of connector between ifs so I can have multiple results.

 

Any help or alternative is appreciated.

 

 

4 Replies

    • Roxana_Menendez's avatar
      Roxana_Menendez
      Copper Contributor
      =TEXTJOIN(", ",TRUE,IF(F7="Not eligible","QA",""),IF(I7="Not eligible","Tardies",""),IF(M7="Not eligible","Break & Lunch",""),IF(N7="Not eligible","Personal",""),IF(O7="Not eligible","Absences",""))

      Was the answer. Thank you!
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        It could be any row. But did not share your workbook, so I made my assumptions.
        Or even hardcoded:
        {"QA"."".""."Tardies".""."".""."Break & Lunch"."Personal"."Absences"}

Resources