# Using IF or IFS function with named ranges

Highlighted
Occasional Visitor

# Using IF or IFS function with named ranges

I'm trying to write a formula that will populate a cell based on the information in another cell.

Stated one way:  "If the value in cell A2 equals the name of any of the people in this named range, then set the value in this cell to "SME", but if the value in cell A2 equals the name of anyone in this other named range, then set the value in this cell to "L3", etc.

Stated another way:

If the value in [cell] is [any of the values in SME named range], then "SME"; If the value in [cell] is [any of the values in the L3_ named range], then "L3"; If the value in [cell] is [any of the values in the L2_ named range], then "L2"

SME, L3_ and L2_ are all named ranges that include the names of people.

I haven't been able to get a legitimate value from any of my attempts, so either I'm missing something (most likely) or what I'm trying to do can't be done.  I'm trying to avoid a lengthy formula that has multiple "OR" statements embedded because at least one of the named ranges has 20 values in it.

If you have questions on this, if what I'm asking isn't clear, let me know.  I appreciate any help.

Thanks,
Cherie

2 Replies
Highlighted

# Re: Using IF or IFS function with named ranges

Try this:
=IF(COUNTIF(SME,A2),”SME”,
IF(COUNTIF(L3_,A2),”L3”,
IF(COUNTIF(L2_,A2),”L2”,
`=IFS(COUNTIFS(sme,A2),"SME",COUNTIFS(le,A2),"LE",TRUE, "nothing")`