INDEX MATCH with three variables?

Hi,

I'm trying to extract data from the massive World Bank spreadsheets and having a few issues. The sheet I'm copying from has about 400 statistical series for 200 countries, arranged by series then country name, with the figues in columns to the right, i.e.:

Series - Country - 1990 - 1991 - 1992....

GDP - USA - 5 - 10 - 15...
GDP - UK - 4 - 6 - 8...
...
Pop - USA - 1 - 2 - 3...
Pop - UK - 2 - 10 - 50...

I want to pull out all the GDP figures for a range of years and a selection of countries into a new sheet, matching on Series name, country name and year. I thought I could use INDEX MATCH for this, but I can't work out how to add the third variable I'm trying to find, has anyone got any suggestions?

Thanks for any help.

With your version of Excel, if you have unique listing of Series/Countries and the annual stats are numeric in the table, then you can use SUMIFS

e.g

=SUMIFS(INDEX('Sheet1'!$A$1:$X$100,0,MATCH($C1,'Sheet1'!$A$1:$X$1,0)),'Sheet1'!$A$1:$A$100,$A1, $B$1:$B$100,$B1)

where your table is in Sheet1, Column A1:X100, and your variables are in active sheet, A1, B1, and C1 for Series, Country and Year, respectively.

Adjust as necessary.


If you want to use just INDEX/MATCH.. this regularly entered formula might work:

=INDEX('Sheet1'!$A$1:$X$100,MATCH(1,INDEX(('Sheet1'!$A$1:$A$100=$A1)*('Sheet1'!$B$1:$B$100=$B1),0),0),MATCH($C1,'Sheet1'!$A$1:$X$1,0))

with same conditions as before....