Excel formula to return cell value if multiple criteria met

I am trying to automate the copying of long term economic data from one spreadsheet (the original data source) to a new workbook that tracks changes over the past decade only.

The first image is of the original data source. Each economic region (ECO1, ECO2) has its own unique Series ID:

The second image is of the new workbook:

I would like to insert a formula into the second workbook that matches the listed Series ID to the original dataset, then matches the listed date in the same manner, in order to return the relevant cell value (e.g. if the Series ID is A2336355R and the date is June 2007, the formula returns the value 1.7 as listed in cell F20 of the original workbook).

Such a formula would allow me to simply change the dates in the new workbook (e.g. changes over the previous ten years only) and have the cell values update accordingly.

2 Answers

this can be done easily with a combination of vlookup

vlookup's input requires the lookup value, the array, and the index column

that index column can be automated using hlookup, i.e. it will tell vlookup which column to return the value based on the Series ID

then simply use vlookup to match the date to the column

easy peasy

It appears that using an INDEX MATCH MATCH formula answers my query.

Specifically, the following formula copies data from the original workbook to the second one, and will update all figures if I change the dates or Series IDs in the second workbook:

=INDEX('[original-data-source.xlsx]Data1'!$B$3:$DW$100,MATCH($A5,'[original-data-source.xlsx]Data1'!$A$3:$A$100,0),MATCH(B$4,'[original-data-source.xlsx]Data1'!$B$2:$DW$2,0))

This formula is broken down further below. In short, it is essentially:

• INDEX - Select the array that you wish to pull data from (in the case above, the original data source workbook)
• MATCH (both times) - Select the value (i.e. cell) in the second workbook that you want Excel to find in the original data source workbook, then select the array where Excel will look to find the value, and then put '0' in as the last argument to ensure an exact match

In the formula above, the INDEX has selected all the cells from B3 across and down in the original data source workbook.

The first MATCH function selects the date in the second workbook, then searches for an exact match in column A of the original data source workbook.

The second MATCH function selects the Series ID in the second workbook, then searches for an exact match in row 2 of the original data source workbook.