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)

		Array1							Array2
	6	7	8	9	10			60	70	80	90	100
1	A	B	C	D	E		10	1	2	3	4	5
2	F	G	H	I	J		20	6	7	8	9	10
3	K	L	M	N	O		30	11	12	13	14	15
4	P	Q	R	S	T		40	16	17	18	19	20
5	U	V	W	X	Y		50	21	22	23	24	25

<tbody>
</tbody>

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