Index Match Match from a named table to a range

[szita2000]

Hi Lads.

New year new challenge.

TLDR: Converted range to a named table and all Index Match Match formulas stopped working from the new table

Back story: I need to make queries to numerous excel file in a folder, where we did put some tables (Not formatted as tables) but the same structure in case we will need to query them later.
Offcourse our end users messed up the tables so I have to resort to put named tables in to a sheet in the back somewhere that is very hidden and I am planning to pull the fresh "linked" tables with my query.
(Those will be the same structure throughout)
So I went in created the data, pulled in all rows, columns I wanted with good old fashioned INDEX MATCH MATCH and when I converted the range to a table all of my values bring back a N/A error.
Since the Named Table is now a structured data.
So I went ahead and changed my old INDEX MATCH MATCH lookup values so it points to the column names, but I am unable to lock the lookup values to column or row as you would with your Match formulas in an Index Match Match.
What is the syntax to lock the [@[Measures (ENG)]] column in this formula?
And the [@[03/07/2023]] to the first row (That's where my headers are in freshly converted named table)


So my formula in E2 is this:
=INDEX('RTT Scorecard'!$H$4:$BS$51, MATCH([@[Measures (ENG)]], 'RTT Scorecard'!$D$4:$D$51, 0), MATCH([@[03/07/2023]], 'RTT Scorecard'!$H$1:$BS$1, 0))

If I drag it to F2 it drags the lookup value in the first match to the column header next to it:
=INDEX('RTT Scorecard'!$H$4:$BS$51, MATCH([@[03/07/2023]], 'RTT Scorecard'!$D$4:$D$51, 0), MATCH([@[10/07/2023]], 'RTT Scorecard'!$H$1:$BS$1, 0))

How is it working when we want to make lookups from Named Tables? Am I missing something new and fancy that does this?

Thanks

 

[Alex Blakenburg]

Don't use the mouse to Drag it. Either user copy paste or highlight both column and use Ctrl+R (right fill)
Just make sure you do the whole column inside the table or the formula won't become part of the table properties and won't fill down.