 • 595K Members
• 6,679 Online
• 724K Conversations
SOLVED

Highlighted

# 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") 6 Replies
Highlighted

# Re: IFS formula returning #NAME?

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

Highlighted

# Re: IFS formula returning #NAME?

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

# Re: IFS formula returning #NAME?

I am using Excel 2016

Highlighted

# Re: IFS formula returning #NAME?

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

Highlighted
Solution

# Re: IFS formula returning #NAME?

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.

Highlighted

# Re: IFS formula returning #NAME?

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

Related Conversations