SOLVED

Use multiple IF functions in one cell without nesting

%3CLINGO-SUB%20id%3D%22lingo-sub-2670014%22%20slang%3D%22en-US%22%3EUse%20multiple%20IF%20functions%20in%20one%20cell%20without%20nesting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2670014%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20even%20know%20if%20this%20is%20possible%20but%20here's%20what%20I'm%20trying%20to%20do%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20and%20in%20cells%20F%2C%20I%2C%20M%2C%20N%20and%20O%20have%20a%20formula%20that%20either%20puts%20a%20value%20or%20a%20%22not%20eligible%22%20phrase.%20What%20I'm%20trying%20to%20do%20is%20put%20in%20cell%20Q%20the%20description%20why%20it%20is%20%22not%20eligible%22%20and%20it%20can%20have%20multiple%20results%2C%20so%20a%20nested%20IF%20would%20not%20work%20(I%20think).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20%22ideally%22%20the%20formula%20should%20be%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIF%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EF7%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22Not%20eligible%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20string%20%22%3E%22QA%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20string%20%22%3E%22%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%2CIF(I%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22Not%20eligible%22%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22%20string%20%22%3E%22Tardies%22%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22%20string%20%22%3E%22%22%3C%2FSPAN%3E)%2CIF(M%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22Not%20eligible%22%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22%20string%20%22%3E%22Break%20%26amp%3B%20Lunch%22%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22%20string%20%22%3E%22%22%3C%2FSPAN%3E)%2CIF(N%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22Not%20eligible%22%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22%20string%20%22%3E%22Personal%22%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22%20string%20%22%3E%22%22%3C%2FSPAN%3E)%2CIF(O%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22Not%20eligible%22%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22%20string%20%22%3E%22Absences%22%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22%20string%20%22%3E%22%22%3C%2FSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Ei.e%3A%20if%20in%20cell%20F%2C%20M%2C%20and%20O%20it%20finds%20the%20phrase%20%22not%20eligible%22%20the%20result%20should%20be%3A%20%22QA%2C%20Break%20%26amp%3B%20Lunch%2C%20Absences%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EOf%20course%20the%20formula%20it%20is%20not%20working%20and%20it's%20giving%20me%20a%20%22Formula%20parse%20error%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ESo%20I%20guess%20I'm%20trying%20to%20look%20for%20some%20sort%20of%20connector%20between%20ifs%20so%20I%20can%20have%20multiple%20results.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EAny%20help%20or%20alternative%20is%20appreciated.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2670014%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2670146%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20multiple%20IF%20functions%20in%20one%20cell%20without%20nesting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2670146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1133461%22%20target%3D%22_blank%22%3E%40Roxana_Menendez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2CIF(F7%3AO7%3D%22Not%20eligible%22%2CF%241%3AO%241%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2670161%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20multiple%20IF%20functions%20in%20one%20cell%20without%20nesting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2670161%22%20slang%3D%22en-US%22%3EI'm%20receiving%20%22An%20array%20value%20could%20not%20be%20found%22.%20Why%20F%241%3AO%241%3F%3C%2FLINGO-BODY%3E
New Contributor

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
best response confirmed by Roxana_Menendez (New Contributor)
Solution

@Roxana_Menendez 

=TEXTJOIN(", ",TRUE,IF(F7:O7="Not eligible",F$1:O$1,""))
I'm receiving "An array value could not be found". Why F$1:O$1?
=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!
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"}