IF/AND or COUNTIF with multiple named ranges

[Aviles]

Hi,

I have the below formula that is not working for me when using named ranges:

=IF(AND(V2=STATE,Z2=CITY),"CUST",V2)

STATE and CITY are both named ranges on a separate sheet (STATE= BL2 and CITY= BJ2:BJ10)

When both of the conditions are met/true, the formula is not returning "CUST" like I'm expecting. It's just returning the value in V2.


I also tried using a COUNTIF formula:

=IF(COUNTIF(STATE,V2)+COUNTIF(CITY,Z2),"CUST",V2)

But now it is returning "CUST" when only 1 of the conditions are met (either one).

Any help would be appreciated, thanks.

 

[Cubist]

Which Excel version are you using?
Can you post a sample data?

 

[Sergius]

This should help

=IF(AND(CITY=Z2;STATE=V2);"CUST";V2)

 

[Cubist]

This should help

=IF(AND(CITY=Z2;STATE=V2);"CUST";V2)

You're repeating what the OP have tried. CITY has multiple cells. This is not doing what the OP is expecting.

 

[Aviles]

Which Excel version are you using?
Can you post a sample data?

Hi @Cubist
I'm using Microsoft 365 MSO Version 2402 32-bit
As for the sample data, it's all just simple text values with one State in the State named range, and 9 different City's in the City named range.
Thanks.

 

[KillerOfGiants]

Assuming you are just looking for the existence of the city to be in the list....

=IF(AND(V2=STATE,COUNTIF(CITY,Z2)>0),"CUST",V2)

 

[Cubist]

Still unclear what your expected result is but try:

=IF((V2=STATE)*(Z2=CITY),"CUST",V2)

 

[Aviles]

Assuming you are just looking for the existence of the city to be in the list....

=IF(AND(V2=STATE,COUNTIF(CITY,Z2)>0),"CUST",V2)

Thanks, that worked!

 

[Aviles]

Still unclear what your expected result is but try:

=IF((V2=STATE)*(Z2=CITY),"CUST",V2)

Sorry, should've been more clear. This formula gave me a Spill error, but thanks very much for your help.

 

[Fluff]

I'm using Microsoft 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)