Thread: Retrieving a value from a reference grid according to index values

Problem:

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

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!

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.

Hi Russ911,

Originally Posted by 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.

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.

the formula =INDEX($A$1:$D$5,MATCH(B8,$A$1:$A$5),MATCH(A8,$A$1:$D$1)) is not efficient as it slows down the calculation massively. Is there an alternative?