Home

Using IF or IFS function with named ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-728113%22%20slang%3D%22en-US%22%3EUsing%20IF%20or%20IFS%20function%20with%20named%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728113%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20write%20a%20formula%20that%20will%20populate%20a%20cell%20based%20on%20the%20information%20in%20another%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStated%20one%20way%3A%26nbsp%3B%20%22If%20the%20value%20in%20cell%20A2%20equals%20the%20name%20of%20any%20of%20the%20people%20in%20this%20named%20range%2C%20then%20set%20the%20value%20in%20this%20cell%20to%20%22SME%22%2C%20but%20if%20the%20value%20in%20cell%20A2%20equals%20the%20name%20of%20anyone%20in%20this%20other%20named%20range%2C%20then%20set%20the%20value%20in%20this%20cell%20to%20%22L3%22%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStated%20another%20way%3A%3C%2FP%3E%3CP%3EIf%20the%20value%20in%20%5Bcell%5D%20is%20%5Bany%20of%20the%20values%20in%20SME%20named%20range%5D%2C%20then%20%22SME%22%3B%26nbsp%3BIf%20the%20value%20in%20%5Bcell%5D%20is%20%5Bany%20of%20the%20values%20in%20the%20L3_%20named%20range%5D%2C%20then%20%22L3%22%3B%26nbsp%3BIf%20the%20value%20in%20%5Bcell%5D%20is%20%5Bany%20of%20the%20values%20in%20the%20L2_%20named%20range%5D%2C%20then%20%22L2%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESME%2C%20L3_%20and%20L2_%20are%20all%20named%20ranges%20that%20include%20the%20names%20of%20people.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20been%20able%20to%20get%20a%20legitimate%20value%20from%20any%20of%20my%20attempts%2C%20so%20either%20I'm%20missing%20something%20(most%20likely)%20or%20what%20I'm%20trying%20to%20do%20can't%20be%20done.%26nbsp%3B%20I'm%20trying%20to%20avoid%20a%20lengthy%20formula%20that%20has%20multiple%20%22OR%22%20statements%20embedded%20because%20at%20least%20one%20of%20the%20named%20ranges%20has%2020%20values%20in%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20questions%20on%20this%2C%20if%20what%20I'm%20asking%20isn't%20clear%2C%20let%20me%20know.%26nbsp%3B%20I%20appreciate%20any%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3CBR%20%2F%3ECherie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-728113%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728152%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20or%20IFS%20function%20with%20named%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728152%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DIF(COUNTIF(SME%2CA2)%2C%E2%80%9DSME%E2%80%9D%2C%3CBR%20%2F%3EIF(COUNTIF(L3_%2CA2)%2C%E2%80%9DL3%E2%80%9D%2C%3CBR%20%2F%3EIF(COUNTIF(L2_%2CA2)%2C%E2%80%9DL2%E2%80%9D%2C%3CBR%20%2F%3E%E2%80%9CNot%20Found%E2%80%9D)))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728159%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20or%20IFS%20function%20with%20named%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368301%22%20target%3D%22_blank%22%3E%40cherieb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFS(COUNTIFS(sme%2CA2)%2C%22SME%22%2CCOUNTIFS(le%2CA2)%2C%22LE%22%2CTRUE%2C%20%22nothing%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
cherieb
Occasional Visitor

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
Try this:
=IF(COUNTIF(SME,A2),”SME”,
IF(COUNTIF(L3_,A2),”L3”,
IF(COUNTIF(L2_,A2),”L2”,
“Not Found”)))

@cherieb 

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
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies