You can use the INDEX function to return a value that appears at the intersection of a row and a column. The INDEX function differs from the MATCH function because the INDEX function returns the value that appears in the cell that you identify using the INDEX function; the MATCH function returns the relative row number or column number of the cell that you identify using the MATCH function.
While you can simply specify the row number and column number as you set up the INDEX function, the power of the INDEX function comes from using it in conjunction with the MATCH function. Because the MATCH function returns row numbers and column numbers, you can use the results of the MATCH function as arguments of the INDEX function.
The INDEX function requires three arguments: you must identify the range you want to search, the row number of the range, and the column number of the range. In addition, Excel offers two ways of using the INDEX function: You can use it in its array format or in its reference format. The example in this section shows the array format.
Return the Value
1 Click the cell in which you want to store the function.
2 Click the Formulas tab.
3 Click Insert Function to display the Insert Function dialog box.
4 Type a description here of the function you want to use.
This example uses index.
5 Click Go.
6 Click INDEX.
7 Click OK. The Select Arguments dialog box appears.
8 Click here.
9 Click OK.
The Function Arguments dialog box appears.
10 Click here and select the range containing all of the values to search.
The selected range appears in the Array field.
11 Click here and click the cell containing the row number.
The cell address appears in the Row_num field.
12 Click here and click the cell containing the column number.
The cell address appears in the Column_num field.
13 Click OK.
• Excel displays the result of the formula in the cell you selected in Step 1 as a serial number.
• The formula appears in the Formula bar.
TIPS
How do I use the Reference, row_num, column_num, area_num argument in the Select Arguments dialog box for the INDEX function?
Using this version of specifying arguments, you can select more than one range to search. You can specify multiple ranges, placing the ranges in parentheses separate from other arguments, and then use the Area_num argument to identify which of the ranges you want Excel to search.
The formula, =INDEX((A1:B6,C1:D7),3,2,1), would return the value in cell B3, the cell located at the third row, second column, in the first range.
What formula did you use to set up the MATCH function for the column number?
The lookup value appears in cell G3, and the Lookup_array range is A1:D1.
The formula is =MATCH(G3,A1:D1,0), and the Match_type argument is set to zero to return the location of the first value in the range that equals the lookup value.
Can I combine the INDEX and MATCH functions to display a value in a cell located at a specific intersection?
Yes, you can. Using the worksheet in this example, the formula would be as follows: =INDEX(A1:D27,MATCH(G2,A1:A27,0),MATCH(G3,A1:D1,0)).
Instead of trying to set up this complicated formula, you might want to consider using the Lookup Wizard, as described in the next section.
No Comment