Feb 21 2020 12:53 PM
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")
Feb 21 2020 01:17 PM
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
Feb 21 2020 01:18 PM
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
Feb 21 2020 02:06 PM
I am not sure how to index match but I will try that option.
Feb 21 2020 02:28 PM
SolutionFor 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.
Feb 21 2020 03:01 PM
Thank you, this works great on intermittent cells. I will have to figure out why I am getting #REF! answer in others.
Feb 21 2020 02:28 PM
SolutionFor 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.