If, VLookup, Index, or a combination of all of them?
 

I've gone through many of the posts which answer queries on the above
functions, but haven't found one that I can apply to what I'm trying to do.

There are four columns in particular that I want to analyse in a
multi-column spreadsheet. THe four columns contain several different
numerical codes. I want to return a specific value (which I create myself) in
another column depending on how many of the four columns contain a particular
code and which columns they happen to be.

For example if I'm searching for the Code 1 and these are four of the rows...

Term 1-----Term 2-----Term 3-----Term 4
1 ----- 1 ----- 4 ----- 10
0 ----- 1 ----- 1 ----- 1
1 ----- 2 ----- 1 ----- 0
4 ----- 0 ----- 0 ----- 1

.... what I would like to appear in the column where the formula will be for
each of these four rows is:

07.12 (because the code 1 appears in Terms 1 and 2)
07.234 (because the code 1 appears in Terms 2, 3 and 4
07.13 and so on.)
07.4

I'm using Excel 2003 and while the following formula would do what I'd like
it to in Excel 2007 (at least I think it would... ), I can't use more than 7
nested arguments:

=if(AND(AK2=1,AL2=1,AM2=1,AN2=1),07.1234,if(and(AK 2=1,AL2=1,AM2=1,AN2=0),07.123,if(and(AK2=1,AL2=1,A M2=0,AN2=0),07.12,if(and(AK2=1,AL2=0,AM2=0,AN2=0), 07.1,if(and(AK2=0,AL2=1,AM2=1,AN2=1),07.234,if(and (AK2=0,AL2=0,AM2=1,AN2=1),07.34,if(and(AK2=0,AL2=0 ,AM2=0,AN2=1),07.4,if(and(AK2=0,AL2=1,AM2=1,AN2=0) ,07.23,if(and(AK2=0,AL2=1,AM2=0,AN2=0),07.2,if(and (AK2=0,AL2=0,AM2=1,AN2=0),07.3,if(and(AK2=1,AL2=1, AM2=0,AN2=1),07.124,if(and(AK2=1,AL2=0,AM2=1,AN2=1 ),07.134,if(and(AK2=1,AL2=0,AM2=0,AN2=1),07.14,if( and(AK2=1,AL2=0,AM2=1,AN2=0),07.13,if(and(AK2=0,AL 2=1,AM2=0,AN2=1),07.24,0)))))))))))))))

Any help would be greatly appreciated!

Thank you.

If, VLookup, Index, or a combination of all of them?
 

What result do you want if no cells contain 1?

Try something like this:

="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"")

--
Biff
Microsoft Excel MVP


"LisaM" wrote in message
...
I've gone through many of the posts which answer queries on the above
functions, but haven't found one that I can apply to what I'm trying to
do.

There are four columns in particular that I want to analyse in a
multi-column spreadsheet. THe four columns contain several different
numerical codes. I want to return a specific value (which I create myself)
in
another column depending on how many of the four columns contain a
particular
code and which columns they happen to be.

For example if I'm searching for the Code 1 and these are four of the
rows...

Term 1-----Term 2-----Term 3-----Term 4
1 ----- 1 ----- 4 ----- 10
0 ----- 1 ----- 1 ----- 1
1 ----- 2 ----- 1 ----- 0
4 ----- 0 ----- 0 ----- 1

... what I would like to appear in the column where the formula will be
for
each of these four rows is:

07.12 (because the code 1 appears in Terms 1 and 2)
07.234 (because the code 1 appears in Terms 2, 3 and 4
07.13 and so on.)
07.4

I'm using Excel 2003 and while the following formula would do what I'd
like
it to in Excel 2007 (at least I think it would... ), I can't use more than
7
nested arguments:

=if(AND(AK2=1,AL2=1,AM2=1,AN2=1),07.1234,if(and(AK 2=1,AL2=1,AM2=1,AN2=0),07.123,if(and(AK2=1,AL2=1,A M2=0,AN2=0),07.12,if(and(AK2=1,AL2=0,AM2=0,AN2=0), 07.1,if(and(AK2=0,AL2=1,AM2=1,AN2=1),07.234,if(and (AK2=0,AL2=0,AM2=1,AN2=1),07.34,if(and(AK2=0,AL2=0 ,AM2=0,AN2=1),07.4,if(and(AK2=0,AL2=1,AM2=1,AN2=0) ,07.23,if(and(AK2=0,AL2=1,AM2=0,AN2=0),07.2,if(and (AK2=0,AL2=0,AM2=1,AN2=0),07.3,if(and(AK2=1,AL2=1, AM2=0,AN2=1),07.124,if(and(AK2=1,AL2=0,AM2=1,AN2=1 ),07.134,if(and(AK2=1,AL2=0,AM2=0,AN2=1),07.14,if( and(AK2=1,AL2=0,AM2=1,AN2=0),07.13,if(and(AK2=0,AL 2=1,AM2=0,AN2=1),07.24,0)))))))))))))))

Any help would be greatly appreciated!

Thank you.

If, VLookup, Index, or a combination of all of them?
 

="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"")

adjust cell references to suit


"LisaM" wrote:

I've gone through many of the posts which answer queries on the above
functions, but haven't found one that I can apply to what I'm trying to do.

There are four columns in particular that I want to analyse in a
multi-column spreadsheet. THe four columns contain several different
numerical codes. I want to return a specific value (which I create myself) in
another column depending on how many of the four columns contain a particular
code and which columns they happen to be.

For example if I'm searching for the Code 1 and these are four of the rows...

Term 1-----Term 2-----Term 3-----Term 4
1 ----- 1 ----- 4 ----- 10
0 ----- 1 ----- 1 ----- 1
1 ----- 2 ----- 1 ----- 0
4 ----- 0 ----- 0 ----- 1

... what I would like to appear in the column where the formula will be for
each of these four rows is:

07.12 (because the code 1 appears in Terms 1 and 2)
07.234 (because the code 1 appears in Terms 2, 3 and 4
07.13 and so on.)
07.4

I'm using Excel 2003 and while the following formula would do what I'd like
it to in Excel 2007 (at least I think it would... ), I can't use more than 7
nested arguments:

=if(AND(AK2=1,AL2=1,AM2=1,AN2=1),07.1234,if(and(AK 2=1,AL2=1,AM2=1,AN2=0),07.123,if(and(AK2=1,AL2=1,A M2=0,AN2=0),07.12,if(and(AK2=1,AL2=0,AM2=0,AN2=0), 07.1,if(and(AK2=0,AL2=1,AM2=1,AN2=1),07.234,if(and (AK2=0,AL2=0,AM2=1,AN2=1),07.34,if(and(AK2=0,AL2=0 ,AM2=0,AN2=1),07.4,if(and(AK2=0,AL2=1,AM2=1,AN2=0) ,07.23,if(and(AK2=0,AL2=1,AM2=0,AN2=0),07.2,if(and (AK2=0,AL2=0,AM2=1,AN2=0),07.3,if(and(AK2=1,AL2=1, AM2=0,AN2=1),07.124,if(and(AK2=1,AL2=0,AM2=1,AN2=1 ),07.134,if(and(AK2=1,AL2=0,AM2=0,AN2=1),07.14,if( and(AK2=1,AL2=0,AM2=1,AN2=0),07.13,if(and(AK2=0,AL 2=1,AM2=0,AN2=1),07.24,0)))))))))))))))

Any help would be greatly appreciated!

Thank you.

If, VLookup, Index, or a combination of all of them?
 

"T. Valko" wrote:

What result do you want if no cells contain 1?

If there is no 1, for example, in a row, I'd like the returned value to be 0
(zero).

Try something like this:

="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"")

--
Biff
Microsoft Excel MVP

This works perfect! And it was so simple!!

Thank you very much for your help. :)

If, VLookup, Index, or a combination of all of them?
 

"Teethless mama" wrote:

="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"")

adjust cell references to suit

Thank you very much for your help!

If, VLookup, Index, or a combination of all of them?
 

If there is no 1, for example, in a row,
I'd like the returned value to be 0

Do you mean just plain old 0 or 07.0 ?

For plain old 0:

=IF(COUNTIF(A2:D2,1)=0,0,"07."&IF(A2=1,1,"")&IF(B2 =1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,""))

For 07.0:

="07."&IF(COUNTIF(A2:D2,1)=0,0,"")&IF(A2=1,1,"")&I F(B2=1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"")

--
Biff
Microsoft Excel MVP


"LisaM" wrote in message
...
"T. Valko" wrote:

What result do you want if no cells contain 1?

If there is no 1, for example, in a row, I'd like the returned value to be
0
(zero).

Try something like this:

="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"")

--
Biff
Microsoft Excel MVP

This works perfect! And it was so simple!!

Thank you very much for your help. :)

If, VLookup, Index, or a combination of all of them?
 

These are great! It was 'plain old 0' I was looking for, but I'm learning a
bit more about how to construct formulae (and the logic behind the
construction) from your examples.

Thank you for all your help. :)

Lisa

"T. Valko" wrote:

For plain old 0:

=IF(COUNTIF(A2:D2,1)=0,0,"07."&IF(A2=1,1,"")&IF(B2 =1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,""))

For 07.0:

="07."&IF(COUNTIF(A2:D2,1)=0,0,"")&IF(A2=1,1,"")&I F(B2=1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"")

--
Biff
Microsoft Excel MVP

If, VLookup, Index, or a combination of all of them?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"LisaM" wrote in message
...
These are great! It was 'plain old 0' I was looking for, but I'm learning
a
bit more about how to construct formulae (and the logic behind the
construction) from your examples.

Thank you for all your help. :)

Lisa

"T. Valko" wrote:

For plain old 0:

=IF(COUNTIF(A2:D2,1)=0,0,"07."&IF(A2=1,1,"")&IF(B2 =1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,""))

For 07.0:

="07."&IF(COUNTIF(A2:D2,1)=0,0,"")&IF(A2=1,1,"")&I F(B2=1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"")

--
Biff
Microsoft Excel MVP