Index Match with three variables in Excel

问题:

In the workbook on the left I have a list of Years (column A) and Country Codes (column B), and then on the top row a long list of World Bank development indicator names (columns C, D, E, etc.). In the workbook on on the right I have the values for each indicator for each year and each country. What I would like to do is to use a formula (index match or something else) in order to get the values for the indicators for the corresponding years and countries. Could anyone provide me with the formula that would solve this problem so I can apply it to all rows and columns? Thank you in advance for your help!:)

回答1:

its an ARRAY Formula. You need to press CTRL + SHIFT + ENTER

{=INDEX($C$4:$E$15,MATCH(1,(A4:A15=$A$25)*(B4:B15=B25),0),MATCH(C24,C3:E3,0))}