Multiple Nested IF functions if cells are blank

%3CLINGO-SUB%20id%3D%22lingo-sub-1460171%22%20slang%3D%22en-US%22%3EMultiple%20Nested%20IF%20functions%20if%20cells%20are%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1460171%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20return%203%20different%20conditions%2Fstatus%20based%20on%20the%20value%20of%203%20different%20cells.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%203%20blank%20cells%20in%20excel%20and%20i%20want%20the%20status%20to%20change%20based%20on%20values%20being%20entered%20into%20other%20cells.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EE.g.%20If%20a%20value%20is%20entered%20in%20cell%20A1%20the%20status%20in%20cell%20D1%20%3D%20%22New%20Request%22%2C%20If%20a%20value%20is%20entered%20in%20cell%20B1%20%3D%20%22In%20Progress%22%20and%20if%20a%20value%20is%20entered%20in%20Cell%20C1%20%3D%20%22Complete%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1460171%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1460281%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Nested%20IF%20functions%20if%20cells%20are%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1460281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698181%22%20target%3D%22_blank%22%3E%40Adelee%3C%2FA%3E%26nbsp%3B%20I%20think%20I%20get%20what%20you%20want%20and%20there%20are%20a%20number%20of%20ways%20to%20do%20it.%26nbsp%3B%20Part%20of%20it%20will%20depend%20on%20possible%20%22error%22%20cases.%26nbsp%3B%20Is%20it%20possible%20someone%20will%20fill%20in%20cell%20C1%20without%20A1%20or%20B1%3F%26nbsp%3B%20If%20so%20what%20should%20be%20displayed%3F%26nbsp%3B%20Also%20some%20people%20have%20preferences%20based%20on%20'readability'.%26nbsp%3B%20So%20here%20is%20a%20nice%20simple%20formula%20that%20basically%20chooses%20the%20state%20based%20on%20how%20many%20of%20those%20cells%20are%20not%20blank%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCHOOSE(SUM(--NOT(ISBLANK(A1))%2C--NOT(ISBLANK(B1))%2C--NOT(ISBLANK(C1)))%2B1%2C%22%22%2C%22New%20Request%22%2C%22In%20Progress%22%2C%22Complete%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ebut%20that%20may%20not%20handle%20those%20error%20cases%20the%20way%20you%20want.%26nbsp%3B%20So%20here%20is%20using%20IFS%20statement%20going%20from%20right%20to%20left%20and%20once%20a%20value%20is%20found%20it%20uses%20that%20state%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFS(NOT(ISBLANK(C1))%2C%22Complete%22%2CNOT(ISBLANK(B1))%2C%22In%20Progress%22%2CNOT(ISBLANK(A1))%2C%22New%22%2CTRUE%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eif%20you%20will%20only%20be%20looking%20for%20text%20in%20cols%20A%2CB%2CC%20then%20you%20could%20just%20check%20if%20the%20cell%20is%20%26gt%3B%22%22%20and%20I%20actually%20like%20to%20use%20%26gt%3B%22%20%22%20because%20sometimes%20people%20hit%20%3CSPACE%3E%20to%20'clear'%20a%20cell%20instead%20of%20delete.%26nbsp%3B%20Also%2C%20here%20is%20cascading%20IF%20statement%20in%20case%20you%20don't%20have%20the%20IFS%20or%20just%20find%20it%20cleaner%3A%3C%2FSPACE%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(C1%26gt%3B%22%20%22%2C%22Complete%22%2CIF(B1%26gt%3B%22%20%22%2C%22In%20Progress%22%2CIF(A1%26gt%3B%22%20%22%2C%22New%22%2C%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1460595%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Nested%20IF%20functions%20if%20cells%20are%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1460595%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3EThank%20you%20so%20much.%20The%20first%20formula%20does%20exactly%20what%20i%20wanted.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1460625%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Nested%20IF%20functions%20if%20cells%20are%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1460625%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698181%22%20target%3D%22_blank%22%3E%40Adelee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(LOOKUP(2%2C1%2F(A1%3AC1%26lt%3B%26gt%3B%22%22)%2C%7B%22New%20Request%22%2C%22In%20Progress%22%2C%22Complete%22%7D)%2C%22no%20status%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, 

 

I am trying to return 3 different conditions/status based on the value of 3 different cells. 

 

I have 3 blank cells in excel and i want the status to change based on values being entered into other cells. 

 

E.g. If a value is entered in cell A1 the status in cell D1 = "New Request", If a value is entered in cell B1 = "In Progress" and if a value is entered in Cell C1 = "Complete"

3 Replies
Highlighted

@Adelee  I think I get what you want and there are a number of ways to do it.  Part of it will depend on possible "error" cases.  Is it possible someone will fill in cell C1 without A1 or B1?  If so what should be displayed?  Also some people have preferences based on 'readability'.  So here is a nice simple formula that basically chooses the state based on how many of those cells are not blank:

=CHOOSE(SUM(--NOT(ISBLANK(A1)),--NOT(ISBLANK(B1)),--NOT(ISBLANK(C1)))+1,"","New Request","In Progress","Complete")

but that may not handle those error cases the way you want.  So here is using IFS statement going from right to left and once a value is found it uses that state:

=IFS(NOT(ISBLANK(C1)),"Complete",NOT(ISBLANK(B1)),"In Progress",NOT(ISBLANK(A1)),"New",TRUE,"")

if you will only be looking for text in cols A,B,C then you could just check if the cell is >"" and I actually like to use >" " because sometimes people hit <space> to 'clear' a cell instead of delete.  Also, here is cascading IF statement in case you don't have the IFS or just find it cleaner:

=IF(C1>" ","Complete",IF(B1>" ","In Progress",IF(A1>" ","New","")))

 

@mtarlerThank you so much. The first formula does exactly what i wanted.  

Highlighted

@Adelee 

As variant

=IFNA(LOOKUP(2,1/(A1:C1<>""),{"New Request","In Progress","Complete"}),"no status")