• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to capture a 3 x 3 array using a dynamic center point from a simple pivot table or larger array?

AllanW

New Member
Hello there,

I've searched around the internet and this forum and found no answer to my problem.
I want to return a 3 x 3 array from preferentially a simple pivot table or alternatively from a larger array.
The return array should use a dynamic center point.

I have spent hours trying to find a solution using GETPIVOTDATA, Index Match and Offset Match but some #REF! or #N/A errors occur.
The really strange part is that errors are generated only when trying to reference columns with specific values of either 1.10 or 1.15 that can be the center point or at the shoulders.

Attached is a worksheet of my attempts to find a solution with some embedded info.
I hope that it is understood what I am trying to achieve.

Looking forward to some help.

Thanks,
AllanW
 

Attachments

  • Chandoo Post Array Problem.xlsx
    20.7 KB · Views: 6
AllanW
..- change the last 0 to 1 from Your index-formula.
eg cell R18 =INDEX($F$5:$L$9,MATCH(O18,$E$5:$E$9,0),MATCH(R17,$F$4:$L$4,1))
Seems work too.
 
Thanks for the response.
Your solution will work most of the time but it is not looking for an exact match which is what I require.
@bosco_yip is 100% correct in stating that it is a floating point problem so the rounding he suggested corrects that and still points to an exact match.

It is a wonderful forum you have here; quick answers.

AllanW
..- change the last 0 to 1 from Your index-formula.
eg cell R18 =INDEX($F$5:$L$9,MATCH(O18,$E$5:$E$9,0),MATCH(R17,$F$4:$L$4,1))
Seems work too.
 
AllanW
Of course, I couldn't check all variations - that's why - seems work too.
An exact match will be the best .. and ... maybe with lower value (1.05) too?
Num_digits-parameter could be 2, but it seems to work with bigger values too.
 
Back
Top