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

**JTwrk** · 11-05-2011, 04:09 AM

I know you can make the colum in an index match function variable by using a 0 for the row index. But in the match function in the column field, when selecting the lookup array for your headers is it possible to make that variable where it searches for a value and then wherever it finds that value it uses that row as the lookup array.

Please see my spreadsheet, it makes it very clear what I am trying to achieve and is well layed out.

more detailed explanation below:

The Index match function below has dynamic column selection in that rather than selecting the column range for criteria or to sum, it finds the proper column from the headers in the table (columns can be in any order) Is it possible to make it so that in the following match function: MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0), the lookup array or "'Raw Data'!$1:$1" portion is variable. Meaning that rather than having to manually choose or lock in the row 1:1 as the header lookup array, instead the formula searches for the word/header "House" in the raw data worksheet, then whatever row it finds the word house in, it designates as the row for the lookup array. This way if the raw Data export was pasted in such that the headers were in row 2 or 4 or 47, it would still find the data and select the proper row for the lookup array and thus the formulas would continue to work properly. The headers are unique and would only appear in the raw data once, and only be used as the header. Is what i am trying to achieve possible

Again for a clearer understanding view the attached spreadsheet and read/follow the two text boxes and it will be exactly clear what I am trying to accomplish

Thank you for any help

**Marcol** · 11-05-2011, 09:34 AM

I'm not sure if I follow you correctly...

Does this workbook, Sheet "Table" Table 1, do as you are expecting?

**JTwrk** · 11-05-2011, 03:26 PM

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.