Match formula or other

[jcaptchaos2]

This should be easy and probably is but I am drawing a blank, I have a pivot table that will grow and shrink with data, all I am trying to do is insert a formula in "A5" to look at a range "B5:BB100" and find "Grand Total" there is a "Grand Total" in row 5 and a "Grand Total" in column "B". I am familiar with vlookup and hlookup but since the row and column that the work "Grand Total" is in will always be changing it won't work.

 

[Steve=True]

Index and match will work for this:

This pivot table goes from a4:d10

=INDEX(A4:D10,MATCH("Grand Total",A4:A10,0),MATCH("Grand Total",A4:D4,0))

Returned the correct value for the grand total at the bottom right of the pivot table.

 

[jcaptchaos2]

Thanks can not get this to work getting N/A

 

[Steve=True]

i didn't modify it for your table structure, so try this:

=INDEX(b5:bb100,MATCH("Grand Total",b54:b100,0),MATCH("Grand Total",b5:bb5,0))

 

[jcaptchaos2]

Yes that works I think you ment 5 instead of 54 in the middle but with changing that it works great. Thanks for you help.