Forum Discussion
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"),"Moder...
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.
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
I am not sure how to index match but I will try that option.
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.
Thank you, this works great on intermittent cells. I will have to figure out why I am getting #REF! answer in others.