Formula help needed; IF or IFS?

%3CLINGO-SUB%20id%3D%22lingo-sub-1544440%22%20slang%3D%22en-US%22%3EFormula%20help%20needed%3B%20IF%20or%20IFS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544440%22%20slang%3D%22en-US%22%3E%3CP%3EAF2%2C%20AF6%2C%20AF10%20and%20AF16%20have%20several%20zip%20codes%20separated%20by%20a%20comma.%20I%20know%20V2%20is%20listed%20in%20AF16%20but%20I%20keep%20getting%20False%20as%20the%20answer.%20How%20can%20I%20correct%20this%20formula%3F%20Using%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(V2%3DAF2%2C%22Coastal%22%2CIF(V2%3DAF6%2C%22Zone%20A%22%2CIF(V2%3DAF10%2C%22Zone%20B%22%2C(IF(V2%3DAF16%2C%22Zone%20C%22)))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1544440%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544648%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20needed%3B%20IF%20or%20IFS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544648%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739309%22%20target%3D%22_blank%22%3E%40YvetteGarza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20formula%20is%20correct%2C%20something%20is%20with%20values.%20Try%20in%20any%20empty%20cell%20%3DV2%3DAF16%20if%20it%20returns%20TRUE%20or%20FALSE%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544676%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20needed%3B%20IF%20or%20IFS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20zip%20codes%20like%20this%20in%20those%20cells.%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%2264%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E77414%2C77422%2C77465%2C77534%2C77541%2C77550%2C77551%2C77554%2C77563%2C77577%2C77623%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1545692%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20needed%3B%20IF%20or%20IFS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545692%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739309%22%20target%3D%22_blank%22%3E%40YvetteGarza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20669px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207864i3C962BE582EF8279%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(V2%3D%22%22%2C%22zip%20is%20not%20defined%22%2C%0A%20IF(COUNTIF(%24AE%242%2C%22*%22%26amp%3BV2%26amp%3B%22*%22)%2C%22Coastal%22%2C%0A%20IF(COUNTIF(%24AE%246%2C%22*%22%26amp%3BV2%26amp%3B%22*%22)%2C%22Zone%20A%22%2C%0A%20IF(COUNTIF(%24AE%2410%2C%22*%22%26amp%3BV2%26amp%3B%22*%22)%2C%22Zone%20B%22%2C%0A%20IF(COUNTIF(%24AE%2416%2C%22*%22%26amp%3BV2%26amp%3B%22*%22)%2C%22Zone%20C%22%2C%0A%20%22wrong%20zip%22%0A)))))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549959%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20needed%3B%20IF%20or%20IFS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549959%22%20slang%3D%22en-US%22%3ENope%20didn't%20work.%20Thanks%20so%20much%20for%20trying%20though.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1550021%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20needed%3B%20IF%20or%20IFS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1550021%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EYAYYYY!%20Success%2C%3C%2FSTRONG%3E%20I%20changed%20the%20%22wrong%20zip%22%20to%20%22Not%20Listed%22%20but%20other%20than%20that%2C%20your%20formula%20worked.%20Now%20I%20need%20to%20learn%2Funderstand%20the%20reasoning%20behind%20this%20so%20I%20can%20retain%20the%20thought%20process.%3C%2FP%3E%3CP%3EThank%20you%20so%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551438%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20needed%3B%20IF%20or%20IFS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739309%22%20target%3D%22_blank%22%3E%40YvetteGarza%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%20Not%20sure%20how%20you%20reproduced%20the%20formula%2C%20better%20to%20take%20not%20from%20post%20but%20from%20the%20file%20attached%20to%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

AF2, AF6, AF10 and AF16 have several zip codes separated by a comma. I know V2 is listed in AF16 but I keep getting False as the answer. How can I correct this formula? Using 365.

 

=IF(V2=AF2,"Coastal",IF(V2=AF6,"Zone A",IF(V2=AF10,"Zone B",(IF(V2=AF16,"Zone C")))))

6 Replies
Highlighted

@YvetteGarza 

In general formula is correct, something is with values. Try in any empty cell =V2=AF16 if it returns TRUE or FALSE

Highlighted

@Sergei Baklan 

I have zip codes like this in those cells. 

77414,77422,77465,77534,77541,77550,77551,77554,77563,77577,77623
Highlighted

@YvetteGarza 

Perhaps like this

image.png

with

=IF(V2="","zip is not defined",
 IF(COUNTIF($AE$2,"*"&V2&"*"),"Coastal",
 IF(COUNTIF($AE$6,"*"&V2&"*"),"Zone A",
 IF(COUNTIF($AE$10,"*"&V2&"*"),"Zone B",
 IF(COUNTIF($AE$16,"*"&V2&"*"),"Zone C",
 "wrong zip"
)))))
Highlighted
Nope didn't work. Thanks so much for trying though.
Highlighted

@Sergei Baklan 

YAYYYY! Success, I changed the "wrong zip" to "Not Listed" but other than that, your formula worked. Now I need to learn/understand the reasoning behind this so I can retain the thought process.

Thank you so much!!

Highlighted

@YvetteGarza , you are welcome. Not sure how you reproduced the formula, better to take not from post but from the file attached to it.