Combining INDEX and MATCH in Excel 2007:-
Initially, the INDEX function doesn’t seem that useful. Its syntax, which is used in a later chart example, is as follows:
=INDEX(Rectangular Range, Which Row in the Range, Which Column in the Range)
=INDEX(A1; Z26, 5, 10) returns the value at the 5th row and 10th column of the range A1:Z26. There are certainly easier ways to refer to cell J5 (for example, =J5).
The INDEX function becomes powerful when you use a MATCH function as the second argument to specify which range to return.
In the Figure (given below), a processor-intensive MATCH function in cell D1 finds the row number where the sales rep can be found. Relatively fast INDEX functions in cells D2 and D3 then return the value from that row in the quota or sales column.
(Examples of the MATCH and INDEX functions.)
The next three charting examples make use of the OFFSET, VLOOKUP, INDEX, and/or MATCH functions to change a chart in response to changes in the underlying worksheet.
Combining INDEX and MATCH in Excel 2007
Linear Mode
