Tip Printed from ExcelTip.com

---

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))}