Hi,

I have nine columns say column A to column I and each of them contain
some data and I have another column called "Code".
If column A is not empty ( even though the other column contain data)
then code=A
If column A is empty but column A is not empty then Code=B
......and so on.
For cells empty in column A through column I, I use Code"excluded"
I use formula
=IF((A2<""),"A",IF((b2<""),"B",IF((c2<""),"C",I F((d2<""),"D",if((e2<""),"E",if((f2<""),"F",If( (g2<""),"G",if((h<""),"H",
if((i2<""),"i","excluded")))))))))

But I found I can't have over 7 multiple ifs.

Can anyone give me a advice? How to fix my formula?

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):


=IF(COUNTA(A1:I1),CHAR(64+MATCH(FALSE,ISBLANK(A1:I 1),FALSE)),"Excluded")



In article . com,
wrote:

Hi,

I have nine columns say column A to column I and each of them contain
some data and I have another column called "Code".
If column A is not empty ( even though the other column contain data)
then code=A
If column A is empty but column A is not empty then Code=B
.....and so on.
For cells empty in column A through column I, I use Code"excluded"
I use formula
=IF((A2<""),"A",IF((b2<""),"B",IF((c2<""),"C",I F((d2<""),"D",if((e2<""),"
E",if((f2<""),"F",If((g2<""),"G",if((h<"")," H",
if((i2<""),"i","excluded")))))))))

But I found I can't have over 7 multiple ifs.

Can anyone give me a advice? How to fix my formula?

Thanks it works great.
My another question is that my boss want me to show location instead of
A through I.
Like
=IF((A2<""),"SFO",IF((b2<""),"NY",IF((c2<""),"L A",IF((d2<""),"DC",if((e2<""),"
UT",if((f2<""),"WA",If((g2<""),"HI",if((h<"")," JP",
if((i2<""),"CH","excluded")))))))))

Can you help me with it?
Thanks a lot

Jenny


JE McGimpsey wrote:
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):


=IF(COUNTA(A1:I1),CHAR(64+MATCH(FALSE,ISBLANK(A1:I 1),FALSE)),"Excluded")



In article . com,
wrote:

Hi,

I have nine columns say column A to column I and each of them contain
some data and I have another column called "Code".
If column A is not empty ( even though the other column contain data)
then code=A
If column A is empty but column A is not empty then Code=B
.....and so on.
For cells empty in column A through column I, I use Code"excluded"
I use formula
=IF((A2<""),"A",IF((b2<""),"B",IF((c2<""),"C",I F((d2<""),"D",if((e2<""),"
E",if((f2<""),"F",If((g2<""),"G",if((h<"")," H",
if((i2<""),"i","excluded")))))))))

But I found I can't have over 7 multiple ifs.

Can anyone give me a advice? How to fix my formula?

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNTA(A2:I2),CHOOSE(MATCH(FALSE,ISBLANK(A2:I2 ),FALSE),
"SFO","NY","LA","DC","UT","WA","HI","JP","CH"),"ex cluded")

In article . com,
wrote:

Thanks it works great.
My another question is that my boss want me to show location instead of
A through I.
Like
=IF((A2<""),"SFO",IF((b2<""),"NY",IF((c2<""),"L A",IF((d2<""),"DC",if((e2<
""),"
UT",if((f2<""),"WA",If((g2<""),"HI",if((h<"")," JP",
if((i2<""),"CH","excluded")))))))))

Can you help me with it?