Variable col_index_num in vlookup

G

Guest

I have a pivot table with catalogue items in the left hand column and a Grand
Total column which, depending on the data in the pivot table, could be any
number of columns to the right.
How do I always show the Grand Total for each item, regardless of the number
of columns I have?
I have tried vlookup, allowing for the largest conceivable table array, but
need to vary the col_index_num depending on where Grand Total is. How can I
find this?
(I’m not allowed to use macros and I can’t get my hands on the raw data to
manipulate it before it’s put in the pivot table.)
Thanks for any help. I love all you helpful people!!
 
G

Guest

If anybody's interested, I've cracked this...

Use the Match function in combination with Vlookup
Assuming the lookup_value is in Column A, the Pivot Table is on a separate
sheet called PivotTable, and the heading for the Grand Total column will
appear in row 5 of this sheet,
=VLOOKUP(A1,PivotTable!$A$1:$O$50,MATCH("Grand
Total",PivotTable!$A$5:$Z$5,0),TRUE))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic col_index_num in VLOOKUP? 5
vlookup - Col_index_num 10
How do I perform extended calculations in a Pivot Table in Excel 2 4
Grand Total for Rows not Working in a Pivot Table 1
Pivot Table Calculates the Difference in Grand Total 2
Vlookup - Using a named ranged for col_index_num 2
pivot table excel 2
Pivot Table Grand Total for Rows not functioning 1

Top