Forum Discussion

Lgibson500's avatar
Lgibson500
Copper Contributor
Feb 21, 2020
Solved

IFS formula returning #NAME?

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")

 

 

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 21, 2020

    Lgibson500 

    For such sample

    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.

6 Replies

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Feb 21, 2020

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 21, 2020

    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

    and INDEX/MATCH on it

Resources