matrix problem

Hi all,

I can't find a formula to deal with this. The problem is described in the attachment, in column R I want to get those results by using a formula. I have a matrix (A:P) whose cells values are 0 (not seen) or 1 (seen), the columns indicate the years (1996-2011) and in column Q I have the birth years of these individuals (each row is an individual). I want to get the age when they were first time seen.
So, in column R I have that the age when the first individual (first row) was seen is 0, because he was born in 1998 and seen the first time in 1998, the 3rd individual is 2 because he was born in 1996 and seen the first time in 1998 and so on.

Any hint on how to do that?

You could use this formula in R2:

=INDEX(A$1:P$1,MATCH(1,A2:P2,0))-Q2

and then copy down.

Hope this helps.

Pete

Thank you so much,

I was doing it by sorting the birth years and by substituting the values 1996=1 up to 2011=16 and by using a formula with just "match" but it was much more "ugly" and laborious. Thanks to show me how to use function "index" and "match" to do that.

Originally Posted by Pete_UK

You could use this formula in R2:

=INDEX(A$1:P$1,MATCH(1,A2:P2,0))-Q2

and then copy down.

Hope this helps.

Pete

You're welcome - glad to be of help.

Pete