» Retrieving A Value From A Reference Grid, Using Indexes Listed In Another Range
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
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

Book Store:
Recommended Books:
- Writing Excel Macros with VBA, 2nd Edition
- Not-for-Profit Accounting Made Easy
- H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond
- PowerPoint® 2002 For Dummies®
- Marketing Plans
- Business Analysis and Valuation: Using Financial Statements, Text and Cases
No comments have been submitted.


