IFS formula returning #NAME?

[Lgibson500]

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

[#2]

@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

[#3]

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

[#4]

@Sergei Baklan 

I am using Excel 2016

[#5]

@Sergei Baklan 

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

[#6 · Accepted solution]

@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.

[#7]

@Sergei Baklan 

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