» Retrieving A Value From A Reference Grid, Using Indexes Listed In Another Range

Problem:

The numbers in Data Range (A1:E6) are arrayed in a reference grid of 4 columns and 5 rows.
Each row in Index Range (A9:B12) contains a pair of values referring to the row and column of a number in Data Range.
We want to find the number in Data Range referred to by each pair of indexes in Index Range.

Solution 1:

Use the OFFSET and MATCH functions as shown in the following formula:
=OFFSET($A$1,MATCH(B9,$A$1:$A$6,0)-1,MATCH(A9,$A$1:$E$1,0)-1)
Solution 2:

Use the INDEX and MATCH functions as shown in the following Array formula:
{=INDEX($A$1:$E$6,MATCH(B9,$A$1:$A$6),MATCH(A9,$A$1:$E$1))}


Example:

Data Range
________A_______B_______C_______D
1_______9_______1_______28______76
2_______18______7_______56______58
3_______6_______4_______10______43
4_______30______70______2_______12
5_______25______66______34______0




Index Range

Column__________Row_____Value
A_______________4_______30
C_______________3_______10
D_______________5_______0

Screenshot // Retrieving A Value From A Reference Grid, Using Indexes Listed In Another Range