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
| The information in this article applies to: |
| - Microsoft Excel for Windows versions 2.x, 3.0 and 4.0 - Microsoft Excel for OS/2 versions 2.2, 2.21, and 3.0 |
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.
