SOLVED
Home

IFS formula returning #NAME?

%3CLINGO-SUB%20id%3D%22lingo-sub-1187973%22%20slang%3D%22en-US%22%3EIFS%20formula%20returning%20%23NAME%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1187973%22%20slang%3D%22en-US%22%3E%3CP%3EWhy%20would%20this%20formula%20be%20returning%20%23NAME%3F%3C%2FP%3E%3CP%3EIFS(AND(E6%3D%22Rare%22%2CF6%3D%22Low%22)%2C%22Minimal%22%2CAND(E6%3D%22Rare%22%2CF6%3D%22Moderate%22)%2C%22Minimal%22%2CAND(E6%3D%22Rare%22%2CF6%3D%22Critical%22)%2C%22Minimal%22%2CAND(E6%3D%22Rare%22%2CF6%3D%22Catastrophic%22)%2C%22Moderate%22%2CAND(E6%3D%22Unlikely%22%2CF6%3D%22Low%22)%2C%22Minimal%22%2CAND(E6%3D%22Unlikely%22%2CF6%3D%22Moderate%22)%2C%22Minimal%22%2CAND(E6%3D%22Unlikely%22%2CF6%3D%22Critical%22)%2C%22Moderate%22%2CAND(E6%3D%22Unlikely%22%2CF6%3D%22Catastrophic%22)%2C%22Substantial%22%2CAND(E6%3D%22Occasional%22%2CF6%3D%22Low%22)%2C%22Minimal%22%2CAND(E6%3D%22Occasional%22%2CF6%3D%22Moderate%22)%2C%22Moderate%22%2CAND(E6%3D%22Occasional%22%2CF6%3D%22Critical%22)%2C%22Substantial%22%2CAND(E6%3D%22Occasional%22%2CF6%3D%22Catastrophic%22)%2C%22Substantial%22%2CAND(E6%3D%22Likely%22%2CF6%3D%22Low%22)%2C%22Minimal%22%2CAND(E6%3D%22Likely%22%2CF6%3D%22Moderate%22)%2C%22Moderate%22%2CAND(E6%3D%22Likely%22%2CF6%3D%22Critical%22)%2C%22Substantial%22%2CAND(E6%3D%22Likely%22%2CF6%3D%22Catastrophic%22)%2C%22Unacceptable%22%2CAND(E6%3D%22Almost%20Certain%22%2CF6%3D%22Low%22)%2C%22Moderate%22%2CAND(E6%3D%22Almost%20Certain%22%2CF6%3D%22Moderate%22)%2C%22Substantial%22%2CAND(E6%3D%22Almost%20Certain%22%2CF6%3D%22Critical%22)%2C%22Unacceptable%22%2CAND(E6%3D%22Almost%20Certain%22%2CF6%3D%22Catastrophic%22)%2C%22Unacceptable%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Lgibson500_0-1582318342935.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172599i038712B63ED5B8CF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Lgibson500_0-1582318342935.png%22%20alt%3D%22Lgibson500_0-1582318342935.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1187973%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188053%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20formula%20returning%20%23NAME%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188053%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565397%22%20target%3D%22_blank%22%3E%40Lgibson500%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormula%20itself%20works.%20On%20which%20version%20of%20Excel%20you%20are%2C%20is%20IFS%20available%20for%20it%3F%3C%2FP%3E%0A%3CP%3EAs%20a%20minimum%20I'd%20format%20such%20formulas%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFS(%0A%20%20%20AND(E6%3D%22Rare%22%2CF6%3D%22Low%22)%2C%22Minimal%22%2C%0A%20%20%20AND(E6%3D%22Rare%22%2CF6%3D%22Moderate%22)%2C%22Minimal%22%2C%0A%20%20%20AND(E6%3D%22Rare%22%2CF6%3D%22Critical%22)%2C%22Minimal%22%2C%0A%20%20%20AND(E6%3D%22Rare%22%2CF6%3D%22Catastrophic%22)%2C%22Moderate%22%2C%0A%20%20%20AND(E6%3D%22Unlikely%22%2CF6%3D%22Low%22)%2C%22Minimal%22%2C%0A%20%20%20AND(E6%3D%22Unlikely%22%2CF6%3D%22Moderate%22)%2C%22Minimal%22%2C%0A%20%20%20AND(E6%3D%22Unlikely%22%2CF6%3D%22Critical%22)%2C%22Moderate%22%2C%0A%20%20%20AND(E6%3D%22Unlikely%22%2CF6%3D%22Catastrophic%22)%2C%22Substantial%22%2C%0A%20%20%20AND(E6%3D%22Occasional%22%2CF6%3D%22Low%22)%2C%22Minimal%22%2C%0A%20%20%20AND(E6%3D%22Occasional%22%2CF6%3D%22Moderate%22)%2C%22Moderate%22%2C%0A%20%20%20AND(E6%3D%22Occasional%22%2CF6%3D%22Critical%22)%2C%22Substantial%22%2C%0A%20%20%20AND(E6%3D%22Occasional%22%2CF6%3D%22Catastrophic%22)%2C%22Substantial%22%2C%0A%20%20%20AND(E6%3D%22Likely%22%2CF6%3D%22Low%22)%2C%22Minimal%22%2C%0A%20%20%20AND(E6%3D%22Likely%22%2CF6%3D%22Moderate%22)%2C%22Moderate%22%2C%0A%20%20%20AND(E6%3D%22Likely%22%2CF6%3D%22Critical%22)%2C%22Substantial%22%2C%0A%20%20%20AND(E6%3D%22Likely%22%2CF6%3D%22Catastrophic%22)%2C%22Unacceptable%22%2C%0A%20%20%20AND(E6%3D%22Almost%20Certain%22%2CF6%3D%22Low%22)%2C%22Moderate%22%2C%0A%20%20%20AND(E6%3D%22Almost%20Certain%22%2CF6%3D%22Moderate%22)%2C%22Substantial%22%2C%0A%20%20%20AND(E6%3D%22Almost%20Certain%22%2CF6%3D%22Critical%22)%2C%22Unacceptable%22%2C%0A%20%20%20AND(E6%3D%22Almost%20Certain%22%2CF6%3D%22Catastrophic%22)%2C%22Unacceptable%22%2C%0A%20%20%20TRUE%2C%20%22Nothing%22)%0A%20%20%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EBit%20better%20to%20have%20helper%20table%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20380px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172614i1C33B8D55C6C88A0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20INDEX%2FMATCH%20on%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188057%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20formula%20returning%20%23NAME%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EHI%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565397%22%20target%3D%22_blank%22%3E%40Lgibson500%3C%2FA%3E%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20version%20of%20your%20Excel%20IFS%20Function%20%3F%3C%2FP%3E%3CP%3EIFS%20function%20support%20only%26nbsp%3B%3CSPAN%20class%3D%22appliesToItem%22%3EExcel%20Office%20365%20(Mac%20%26amp%3B%20Windows)%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22appliesToItem%22%3EE%3C%2FSPAN%3E%3CSPAN%20class%3D%22appliesToItem%22%3Excel%202019%26nbsp%3B(Mac%20%26amp%3B%20Windows)%20%26amp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22appliesToItem%22%3EExcel%20Online.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ERegards%2C%20Faraz%20Shaikh%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188161%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20formula%20returning%20%23NAME%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188161%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Excel%202016%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188164%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20formula%20returning%20%23NAME%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188164%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20how%20to%20index%20match%20but%20I%20will%20try%20that%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188189%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20formula%20returning%20%23NAME%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188189%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565397%22%20target%3D%22_blank%22%3E%40Lgibson500%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20766px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172627i7E20BFD5C7C5D910%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20in%20G6%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFNA(INDEX(%24J%245%3A%24M%247%2CMATCH(%24F6%2C%24I%245%3A%24I%247%2C0)%2CMATCH(%24E6%2C%24J%244%3A%24M%244%2C0))%2C%22wrong%20data%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EMatrix%20itself%20could%20be%20at%20any%20place%2C%20better%20in%20another%20sheet.%20And%20better%20to%20use%20named%20ranges%20for%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188237%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20formula%20returning%20%23NAME%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%26nbsp%3B%20you%2C%20this%20works%20great%20on%20intermittent%20cells.%20I%20will%20have%20to%20figure%20out%20why%20I%20am%20getting%20%23REF!%20answer%20in%20others.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Why would this formula be returning #NAME?

IFS(AND(E6="Rare",F6="Low"),"Minimal",AND(E6="Rare",F6="Moderate"),"Minimal",AND(E6="Rare",F6="Critical"),"Minimal",AND(E6="Rare",F6="Catastrophic"),"Moderate",AND(E6="Unlikely",F6="Low"),"Minimal",AND(E6="Unlikely",F6="Moderate"),"Minimal",AND(E6="Unlikely",F6="Critical"),"Moderate",AND(E6="Unlikely",F6="Catastrophic"),"Substantial",AND(E6="Occasional",F6="Low"),"Minimal",AND(E6="Occasional",F6="Moderate"),"Moderate",AND(E6="Occasional",F6="Critical"),"Substantial",AND(E6="Occasional",F6="Catastrophic"),"Substantial",AND(E6="Likely",F6="Low"),"Minimal",AND(E6="Likely",F6="Moderate"),"Moderate",AND(E6="Likely",F6="Critical"),"Substantial",AND(E6="Likely",F6="Catastrophic"),"Unacceptable",AND(E6="Almost Certain",F6="Low"),"Moderate",AND(E6="Almost Certain",F6="Moderate"),"Substantial",AND(E6="Almost Certain",F6="Critical"),"Unacceptable",AND(E6="Almost Certain",F6="Catastrophic"),"Unacceptable")

 

Lgibson500_0-1582318342935.png

 

6 Replies
Highlighted

@Lgibson500 

Formula itself works. On which version of Excel you are, is IFS available for it?

As a minimum I'd format such formulas

=IFS(
   AND(E6="Rare",F6="Low"),"Minimal",
   AND(E6="Rare",F6="Moderate"),"Minimal",
   AND(E6="Rare",F6="Critical"),"Minimal",
   AND(E6="Rare",F6="Catastrophic"),"Moderate",
   AND(E6="Unlikely",F6="Low"),"Minimal",
   AND(E6="Unlikely",F6="Moderate"),"Minimal",
   AND(E6="Unlikely",F6="Critical"),"Moderate",
   AND(E6="Unlikely",F6="Catastrophic"),"Substantial",
   AND(E6="Occasional",F6="Low"),"Minimal",
   AND(E6="Occasional",F6="Moderate"),"Moderate",
   AND(E6="Occasional",F6="Critical"),"Substantial",
   AND(E6="Occasional",F6="Catastrophic"),"Substantial",
   AND(E6="Likely",F6="Low"),"Minimal",
   AND(E6="Likely",F6="Moderate"),"Moderate",
   AND(E6="Likely",F6="Critical"),"Substantial",
   AND(E6="Likely",F6="Catastrophic"),"Unacceptable",
   AND(E6="Almost Certain",F6="Low"),"Moderate",
   AND(E6="Almost Certain",F6="Moderate"),"Substantial",
   AND(E6="Almost Certain",F6="Critical"),"Unacceptable",
   AND(E6="Almost Certain",F6="Catastrophic"),"Unacceptable",
   TRUE, "Nothing")
   

Bit better to have helper table like this

image.png

and INDEX/MATCH on it

Highlighted

HI @Lgibson500,

 

What is the version of your Excel IFS Function ?

IFS function support only Excel Office 365 (Mac & Windows), Excel 2019 (Mac & Windows) & Excel Online.

 

Regards, Faraz Shaikh

Highlighted

@Sergei Baklan 

I am using Excel 2016

Highlighted

@Sergei Baklan 

I am not sure how to index match but I will try that option.

Highlighted
Solution

@Lgibson500 

For such sample

image.png

formula in G6 is

=IFNA(INDEX($J$5:$M$7,MATCH($F6,$I$5:$I$7,0),MATCH($E6,$J$4:$M$4,0)),"wrong data")

Matrix itself could be at any place, better in another sheet. And better to use named ranges for it.

Highlighted

@Sergei Baklan 

Thank  you, this works great on intermittent cells. I will have to figure out why I am getting #REF! answer in others.

Related Conversations