Array formula: Finding the latest common available value in multiple time series

[titi3381]

Hi everybody,
I have three time series each with the same 100 countries but of different length depending on the country.

I'm trying to find the latest common year for each country, i.e. the latest year for which the three series have a value for any given country. Something I suspect can be done with Array formulas.

Time series "Population"
Country name 2000 2001 2002 2003
Country A 1.43 1.5 1.9 n/a
Country B 1.5 1.3 n/a n/a

Time series "Life expectancy"
Country name 2000 2001 2002 2003
Country A 80 81 82 84
Country B 70 72 73 75


Time series "Infant mortality"
Country name 2000 2001 2002 2003
Country A 1.43 1.5 3.4 4.6
Country B 1.5 1.3 2.5 n/a

How do I get Excel to tell me that 2002 is the latest year available for all three series in the case of Country A and that 2001 is the latest year available for all three series for Country B?


Many thanks for your help!!!

Thierry