vlookup or index function that will work when column and row numbers are subject to change

[jessiegeorgia]

Hi All,

I've got a pivot table which is refreshed on a weekly basis as new data is added.
I have made a table of vlookups along the following lines;
vlookup_value refs column A
table array is the pivot table
col_index_num refers to a column number in the pivot

=VLOOKUP($A1,'Master Pivot'!$A$5:$ZZ$174,17,FALSE)

As the rows and columns in the pivot table are subject to change when the new data is added the col_index_number isn't appropriate in this instance. Is there a function I can use to accomodate this. The rows of my table are headed and can be used as a lookup ref. I've tried using INDEX but can't see how that works without using column and row num index' either,

Many thanks in advance
Jess

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use the MATCH function to return the column number. Example:

=VLOOKUP($A1,'Master Pivot'!$A$5:$ZZ$174,MATCH("HeaderText",'Master Pivot'!$A$5:$ZZ$5,FALSE),FALSE)

Replace HeaderText with the text that you want to find.

 

[jessiegeorgia]

Perfect, thanks Andrew