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

[LisaM]

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(AK2=1,AL2=1,AM2=1,AN2=0),07.123,if(and(AK2=1,AL2=1,AM2=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,AL2=1,AM2=0,AN2=1),07.24,0)))))))))))))))

Any help would be greatly appreciated!

Thank you.

 

[T. Valko]

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,"")&IF(D2=1,4,"")

 

[Teethless mama]

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

adjust cell references to suit

 

[LisaM]

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,"")&IF(D2=1,4,"")

This works perfect! And it was so simple!!

Thank you very much for your help.

 

[LisaM]

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

adjust cell references to suit

Thank you very much for your help!

 

[T. Valko]

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(A22,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(A22,1)=0,0,"")&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"")

 

[LisaM]

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]

You're welcome. Thanks for the feedback!