Microsoft KB Archive/51206

Referencing Single Values on a Cross-Reference Table in Excel PSS ID Number: Q51206 Article last modified on 02-09-1993 PSS database name: W_eXceL

2.x 3.00 4.00 | 2.20 2.21 3.00

WINDOWS | OS/2

Summary:

The following function returns a value from a table that cross-references two series of data (such as a multiplication table, a commission sheet, and so on)

=INDEX(Table_range,MATCH(Hvalue,Hrange,0),Match(Vvalue,Vrange,0))

where the variables refer to the following:

Variable Refers To ——– ———

Table_range = Reference to values in the table

Hvalue = Cell reference indicating cell where value to find from top of table is located

Hrange = Range of values on top of table

Vvalue = Cell reference indicating cell where value to find from left side of table is located

Vrange = Range of values on left side of table

More Information:

For example, consider the following multiplication table:

A1:    B1:  1   C1:  2    D1:  3   E1:  4   F1:  5 A2: 1  B2:  1   C2:  2    D2:  3   E2:  4   F2:  5 A3: 2  B3:  2   C3:  4    D3:  6   E3:  8   F3: 10 A4: 3  B4:  3   C4:  6    D4:  9   E4: 12   F4: 15 A5: 4  B5:  4   C5:  8    D5: 12   E5: 16   F5: 20 A6: 5  B6:  5   C6: 10    D6: 15   E6: 20   F6: 25 A7:    B7:      C7:       D7:      E7:      F7: A8: 4  B8:  5   C8:       D8:      E8:      F8: To use the values in A8 and B8 to indicate the two values to be cross-referenced on the table, the following formula would be used (assuming A8 is the value searched for along the top of the table and B8 is the value searched for along the left side of the table)

=INDEX(B2:F6,MATCH(A8,B1:F1,0),MATCH(B8,A2:A6,0))

which, in this example, returns 20.

If you want to look for the largest value less than or equal to the values searched for (useful for a commission table, tax table, and so on), change the 0s (zeros) in the embedded MATCH functions to 1s (ones).

Additional reference words: 2.00 2.0 2.01 2.1 2.10 2.2 2.21 2.20 3.00 3.0 4.00 4.0

Copyright Microsoft Corporation 1993.