INDEX MATCH with defined table and multiple criteria

Question

I need to look up a value from a defined table with four criteria. I've done an index match with multiple criteria many time before but I can't seem to get it to work with a defined table instead of cell reference. Here is the formula I've tried, where Test is the name of the Table.

Here's the header and first row of the Table "Test". (excuse the poor formatting)

Location Indicator                                  Scenario    Units  Scale    Measurement 2016    2017    2018    2019    2020    2021
Canada   Exchange rate, period average, per Euro    Aug1        C$ per Euro NA  Level values 1.5 1.4     1.4     1.4     1.4     1.4

Here is the formula:

{=INDEX(Test,MATCH($C31&$E31&$F31&$G31,Test[Scenario]&Test[Location]&Test[Indicator]&Test[Measurement],0),MATCH(H$30,Test[#Headers],0))}

Here is the table I'm trying to Index Match from:

Shock   Shock Name  Country Indicator                             Measurement       2016    2017    2018    2019    2020    2021
Aug1    Aug1        Canada  Exchange rate, period average, per Euro Level values    #N/A    #N/A    #N/A    #N/A    #N/A    #N/A

I'm getting #N/A as my error. When I step through the formula, the calculation goes wrong when the last part of the formula (see below) evaluates to #N/A.

MATCH(H$30,Test[#Headers],0)

Any idea what the problem is? Thanks in advance.

You will need to provide the data, the formula looks correct, but it is not finding a match. as to why we can only guess without the data. — Scott Craner
@ScottCraner Bear with me as this is my first post on here but how can I upload the data on here? — Eddie11

Scott Craner Scott Craner · Accepted Answer · 2017-07-13T18:06:22

The issue is that the values in the header are text values that look like number and not numbers.

Change you MATCH to this:

MATCH(TEXT(H$30,"0000"),Test[#Headers],0)

INDEX MATCH with defined table and multiple criteria

1 Answers