Nested IF not working.....

[Patrick]

Here is the formula that I'm using:

=IF(K2={71876,147825,186110,73859,1183639,163773,140408,226473},"GSA",IF(K2={235842,245561,235823,235843,245337,239135,200830},"CNB",IF(K2={683983,245144,29839,4813,67555,175821,82901,175645,139429,175397,131445,979352,113854,972262,174756,218880,3358},"CB","ADJ")))

When K2 has any of the values listed in the first part of the IF it returns
GSA, like it's supposed to. BUT....if any of the other values listed in the
subsequent parts are in K2 it always list ADJ, like it's not in any of the
other list. I can't get it to return CNB or CB. Any ideas why?

 

[Tom Hutchins]

If I enter 235842 in K2, your formula returns CNB, and if I enter 683983, it
returns CB. I don't know for sure, but I might guess that Excel doesn't like
more than one array of values in brackets in a single formula. It seems to
accept the first value in each subsequent array and ignore the rest. Instead
of the nested IFs, you could enter the numbers and the values they should
return in a couple of empty columns, then use a VLOOKUP formula. If, for
example, you entered your data like this in columns M & N:

71876 GSA
147825 GSA
186110 GSA
73859 GSA
1183639 GSA
16773 GSA
140408 GSA
226473 GSA
235842 CNB
245561 CNB
235823 CNB
etc.

Your Vlookup formula would be

=IF(ISERROR(VLOOKUP(K2,M:N,2,FALSE)),"ADJ",VLOOKUP(K2,M:N,2,FALSE))

If it finds the K2 number in column M, the code next to it in column N is
returned. If the K2 value is not found in column M, ADJ is returned.

Hope this helps,

Hutch

 

[JMB]

When K2 has any value in the first part of the formula other than 71876, it
returns "ADJ" for me.

you'll need to use OR for your conditional tests.
OR(K2={71876,147825,186110,73859,1183639,163773,140408,226473})

 

[Sandy Mann]

You need to enclose your arrays in an OR() function:

=IF(OR(K2={71876,147825,186110,73859,1183639,163773,140408,226473}),"GSA",IF(OR(K2={235842,245561,235823,235843,245337,239135,200830}),"CNB",IF(OR(K2={683983,245144,29839,4813,67555,175821,82901,175645,139429,175397,131445,979352,113854,972262,174756,218880,3358}),"CB","ADJ")))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Patrick]

That worked perfect. Thanks so much. I knew I was close.

 

[JMB]

you're welcome.

That worked perfect. Thanks so much. I knew I was close.