# 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

• 20.7 KB Views: 6

#### bosco_yip

##### Excel Ninja
Floating point problem,

In R17, R23 & R29 add a Round () to your formula :

=ROUND(P14+P15,2)

#### vletm

##### Excel Ninja
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

##### New Member
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.

#### vletm

##### Excel Ninja
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.