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

#### titi3381

##### New Member
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?

Thierry

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Replies
7
Views
419
Replies
0
Views
321
Replies
2
Views
485
Replies
2
Views
753
Replies
2
Views
251

1,196,373
Messages
6,014,897
Members
441,854
Latest member
Amstaff

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back