» 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:
- Mastering Excel 2000 (for beginner)
- The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
- The 22 Immutable Laws of Branding
- The Basics of Finance: Financial Tools for Non Financial Managers
- Fish! A Remarkable Way to Boost Morale and Improve Results
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
trying to find out a formula
Thea
I'm not very good at formulas. I've just stared to use xcel. I need to to put dates in a cell. However, they need to be listed is a cloumn. I would like the last cell to be formulated to count the number of dates that are listed in the column. How do you get the dates to be converted into a general numberic number?
Retrieving a List from a Text reference
hamy72
Hi I have a Purchase Order Report, and it would be of great help if I could have an excel formula that could enable me to retreive a list of items ordered against a certain Purchase order # by mere typing the Purchase Order # in a certain cell.
The attached file contains a portion of the report, with cells A1:B5 the database. However on typing the Order # PU408797 in the cell A16, I would like starting from B16 a list of items ordered on this purchase order # PU408797 listed only.
Regards,
Hamilton Rozario
Multiple Reference output procedure Macro
Brutus
I am trying to automate a milestones past due date register using the qualifying if statement to test if Range H7:H66>0 (based on the system date being greater than the milestone due date).
Please assist me to write a procedure that picks up each entry past due and then extracts the corresponding row references and writes them to cells (in a form) in a different sheet which is a schedule change request.
Thank you
Brutus
=INDEX($A$1:$E$6,MATCH(B10,$A$1:$A$6,0),MATCH(A10,$A$1:$E$1,0))