Lock an index to the correct column based on a value

[MikeGeno]

Working on a file with 2 tabs where I need to use index to reference a value in the pivot table.

I have a pivot table with months down the rows and item numbers across the columns. The Item number is not necessarily in the same column location in my data tab as it is in the pivot table

Pivot Tab

ABC CDE
Jan
Feb
Mar
Apr
May

Data Tab
ABC DEF
Jan
Feb
Mar
Apr
May


I am using the following formula to perform my calculation

='Data'!B6/INDEX('Pivot'!B$8:B$19,MONTH('Data'!$A6))

This gives me the sales for that month divided by the index value generated for that particular month.

When I copy this across at one point my columns are no longer in sync and I am getting the wrong answer.

How can I make the index lock onto the correct column based on the Item number?

 

[Jerry Sullivan]

Using the GETPIVOTDATA formula function with relative references would be a good fit for what you describe.