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))
Screenshot // Retrieving a value from a reference grid according to index values
Index & Match
Valerie wrote on July 18, 2005 16:21 EST
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 wrote on July 22, 2005 11:11 EST
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 wrote on July 26, 2005 01:48 EST
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.
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
Microsoft Access Version 2002 Step by Step
