Dynamic Formula based on two vertical criteria and one horizontal

[GoGators]

Hello,

I'm trying to create a table that will automatically update depending on which country is entered in cell B1, which economic indicator is entered in A4, and which year is in row 3. I have attached a example spreadsheet with how my data is formulated on sheet 2 and what I'm looking for on sheet 1. I have two criteria that search vertically through column A and B on sheet 2, but I want to add another criteria for a vertical

I can currently Index Match with Country and economic indicator, but I hard set my years, so for B4 I hard code to return number from 2011 on my data. My index match formula would be =INDEX(Sheet2!F:F,MATCH(1,($B$1=Sheet2!A:A)*($A$4=Sheet2!B:B),0))


I'm trying to make the Sheet2!F:F reference dynamic to recognize the years in Row 3 in Sheet 1 and match them to the years in Row 1 in Sheet 2.

Forum Example 1.xls

Thank You. Please let me know if I need further explanation.

[benishiryo]

hi there. try this in B4:

Please Login or Register  to view this content.

same like what you have done, CTRL + SHIFT + ENTER. to handle errors in Excel 2003, try:

Please Login or Register  to view this content.

if you have Excel 2007, then:

Please Login or Register  to view this content.

[GoGators]

I used the first formula and it worked. Thank you.