Index match match function issue

Question

I am using INDEX MATCH MATCH to perform a matrix lookup but the following formula returns "#N/A":

=INDEX(Table1,match(Table2[[#Headers],[UK],Table1[Location],0),MATCH([@DATE],Table1[#Headers],0))

Issue seems to be that the dates in my Table1 Header row are being recognised as text. How can I get this to work?

You have too many criteria in your first MATCH, an you are missing a ] somewhere. — Scott Craner
could you mock up some data for each table and expected outcome? — Scott Craner
I am wondering how you've been able to enter these formulas at all. As far as I can tell MATCH takes only three arguments. But your MATCH formulas seem to contain 4 arguments: match(Table2[[#Headers],[UK],Table1[Location],0). This shouldn't give you an #N/A but rather an error when you try to hit enter. — Ralph

Scott Craner Scott Craner · Accepted Answer · 2017-03-10T18:03:53

You are missing a ] after [UK].

When you create a table the headers are changed to text.

So you need to change your search to text that matches the format:

=INDEX(Table1,MATCH(Table2[[#Headers],[UK]],Table1[Location],0),MATCH(TEXT([@DATE],"mm/dd/yyyy"),Table1[#Headers],0))

Change the "mm/dd/yyyy" to the format of the header cells.