|

# » 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

Rate This Tip
 1 2 3 4 5
Rating: 3.54     Views: 32101