» Retrieving a value from a reference grid according to index values
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Range A1:D5 contains numbers, arrayed in a reference grid specified in row 1 (columns) and column A (rows).
How could we find the value in Range A8:D10 matching each pair of index values (column and row) in Range A7:D5?
Solution:
Use the INDEX and MATCH functions, as follows:
=INDEX($A$1:$D$5,MATCH(B8,$A$1:$A$5),MATCH(A8,$A$1:$D$1))
Book Store:
Recommended Books:
Index & Match
Valerie
I use this formula all the time and have created massive spreadsheets to utilize this formula that has cut my work time immensely. I can download text files from our acctg software to piece together reconciliations in spreadsheets for auditors. Don't know how I lived without it!
Grids not Correctly Stated
Russ911
The directions state "How could we find the value in Range A8:D10 ...." There is no number in column D. Thus, shouldn't the directions state Range A8:C10?
Then the direction look to match each pair of index values in Range A7:D5. Row 7 has no values, but contains column titles. Row 6 is blank. Row 5 has data, but what's the relationship? Column D is blank except for D5.
Recommend the entire example be rewritten and that the tips be proofread before publishing.
Reply: Russ911
Alan
Hi Russ911,
[QUOTE=Russ911]The directions state "How could we find the value in Range A8:D10 ...." There is no number in column D. Thus, shouldn't the directions state Range A8:C10?
Then the direction look to match each pair of index values in Range A7:D5. Row 7 has no values, but contains column titles. Row 6 is blank. Row 5 has data, but what's the relationship? Column D is blank except for D5.
Recommend the entire example be rewritten and that the tips be proofread before publishing.[/QUOTE]You didn't mention if you have reported the post.
If not, can I suggest you report it using the link in the post so that the webmaster can re-write it.
Alan.
