Index/Match function: How to make row lookup array dynamic or variable

I'm trying to get it so that no matter where in the raw data worksheet the data is, the formulas in the table work and populate values correctly.

So lets say you selected all the data (including headers) on the raw data worksheet and hit ctrl x and pasted it in starting at cell H1, you would see that table 1 fills out correctly while table 2 fills with errors. Now select all the data in the raw data worksheet again and paste it in starting at cell N4. You will see table 2 populate correctly while table 1 is now filled with errors.

The sumifs & match/index combo formula finds the right location of the columns automatically so it doesn't matter what order the columns are in (plan column can be before house column, date column can be after actual column and so on) as long as the headers are in the row that has been selected as the lookup array for the match part of the formula. Match( ,'Raw Data'!$#:$#, ). So whatever number is in the place of the hashtags in the formula directly to the left, is the row, the headers have to be in for the formula to work. So if the hashtags are replaced with a 5, then the data headers have to start in row 5, if the hashtags are replaced with a 17, the data headers have to be in row 17.

That is the part i am trying to make variable.

Basically I'm trying to proof my spreadsheet against inexperienced users updating it. I want it so that if someone with limited or no excel knowledge gets an export of data from our cost system, they can select the data and they can paste it into the raw data spreadsheet at any point, whether row 8 or row 70 and column A or column Q and the tables will work. I've got the Columns part working right where it doesn't matter what column the data starts in, now i'm trying to get the rows to be variable like that. Named ranges are still locked in place and the person would always have to make sure the data is pasted in to the spreadsheet starting where the named range "DataHeaders" starts


If there was a function that searched the spreadsheet for the the word "House" or whatever header, then returned the row address (1:1, or 15:15) that header was in such that, that row would now be the lookup array in the match formula, then all my problems would be solved.

both columns and rows would be variable such that the data could be pasted anywhere in the spreadsheet and the table formulas would find it properly.