• 470K Members
• 5,434 Online
• 568K Conversations

## Using IF or IFS function with named ranges

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

# 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”,

# Re: Using IF or IFS function with named ranges

It could be like

`=IFS(COUNTIFS(sme,A2),"SME",COUNTIFS(le,A2),"LE",TRUE, "nothing")`
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies