I'm using an INDEX MATCH function to pull seasonal rainfall for ~200 field sites from weather station data into one summary file.
Each weather station is in a different excel file.
Different field sites need data from the weather station closest to them.
I can do it successfully one weather station at a time using the following formula:
=INDEX('PATH\BB.xlsx'!rain,(MATCH($I$4&$I$5,PATH\BB.xlsx'!year&'PATH\BB.xlsx'!season,0)))
where
• PATH = file location (I can type this out as it’s always the same)
• BB.xlsx = Filename.xlsx
• rain, year and season are named ranges in the weather station workbooks
• $I$4 = cell containing the lookup value for year
• $I$5 = cell containing the lookup value for season
However, this means manually changing the filename in the formula for each field site.
I'm therefore trying to write one formula that will automatically go to the correct weather station file to find the correct rainfall value.
I found out about the MOREFUNC add-in from this post and I then based my formula on this post.
This is my formula:
=INDEX(INDIRECT.EXT("'PATH["&$F8&"]seasonaldata'!"&$K$3),MATCH(1,(INDIRECT.EXT("'PATH["&$F8&"]seasonaldata'!"&$K$5)=I5)*(INDIRECT.EXT("'PATH["&$F8&"]seasonaldata'!"&$K$4)=I4),0))
where
• PATH = file location (I can type this out as it’s always the same)
• $F8 = Filename.xlsx (this is the name of the workbook I want to go to)
• seasonaldata = sheet name
• $K$3 = lookup range (in fact K3 is a cell containing the word ‘rain’, which is the name of the lookup range in the weather station files)
• $K$5 = --> including a certain season (i.e. cell K5 contains the name of a range where excel should look for the relevant season)
• $K$4 = --> including a certain year (i.e. cell K4 contains the name of a range where excel should look for the relevant year)
• I5 = cell containing one of the lookup values, which is a name (a season, i.e. winter)
• I4 = cell containing the other lookup value, which is a number (a year, i.e. 2013)
The formula works until the very last part,
'PATH["&$F8&"]seasonaldata'!"&$K$4)=I4
It finds the years and correctly identifies the year I've specified in cell I4, but then returns all FALSE values, i.e. it isn't matching the value I specify in cell I4 with the values it's found in the 'year' range.
What am I doing wrong?
Thank you.
