Use a defined array to derive a column/row of data to use MATCH function in INDEX function

[ShepDubVegas]

I have the following 2 arrays from A2:F7 (defined as Array1) and H2:M7 (defined as Array2)
[TABLE="width: 832"]
<tbody>[TR]
[TD][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"]Array1[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"]Array2[/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]10[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"]60[/TD]
[TD="class: xl65"]70[/TD]
[TD="class: xl65"]80[/TD]
[TD="class: xl65"]90[/TD]
[TD="class: xl65"]100[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"]E[/TD]
[TD][/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]F[/TD]
[TD="class: xl65"]G[/TD]
[TD="class: xl65"]H[/TD]
[TD="class: xl65"]I[/TD]
[TD="class: xl65"]J[/TD]
[TD][/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]K[/TD]
[TD="class: xl65"]L[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]O[/TD]
[TD][/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"]15[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]P[/TD]
[TD="class: xl65"]Q[/TD]
[TD="class: xl65"]R[/TD]
[TD="class: xl65"]S[/TD]
[TD="class: xl65"]T[/TD]
[TD][/TD]
[TD="class: xl65"]40[/TD]
[TD="class: xl65"]16[/TD]
[TD="class: xl65"]17[/TD]
[TD="class: xl65"]18[/TD]
[TD="class: xl65"]19[/TD]
[TD="class: xl65"]20[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]U[/TD]
[TD="class: xl65"]V[/TD]
[TD="class: xl65"]W[/TD]
[TD="class: xl65"]X[/TD]
[TD="class: xl65"]Y[/TD]
[TD][/TD]
[TD="class: xl65"]50[/TD]
[TD="class: xl65"]21[/TD]
[TD="class: xl65"]22[/TD]
[TD="class: xl65"]23[/TD]
[TD="class: xl65"]24[/TD]
[TD="class: xl65"]25[/TD]
[/TR]
</tbody>[/TABLE]

I want to use an INDEX function to look up a value in one of the tables based on the contents of 3 cells, viz,

=INDEX(INDIRECT(A10),MATCH(A11,"column_to_look_up",1),MATCH(A12,"row_to_look_up",1)) where:

* Cell A10 contains the name of the Array I wish to look up (i.e. either Array1 or Array2)
* Cell A11 contains a value that will be looked up to find the correct row (e.g. if 3.1 and looking up Array 1, it would return 4 (4th row in Array1)
* Cell A12 contains a value that will be looked up to find the correct column (e.g. if 96 and looking up Array 2, it would return 5 (5th column in Array2)

I have a workbook with a large number of defined arrays, one of which will be looked up depending on the value in A10. Is their a workaround using OFFSET or similar to utilise the known array address?

Hope this rambling makes sense.

Cheers,

Shep

 

[steve the fish]

Does this work?

=INDEX(INDIRECT(A10),MATCH(A11,INDEX(INDIRECT(A10),0,1),1),MATCH(A12,INDEX(INDIRECT(A10),1,0),1))

 

[ShepDubVegas]

Spot on Fishman! I'd been dancing around it but couldn't get the logic right in my head. Thanks so much - have a great day