Programming and Application(编程与应用)

Excel INDEX and MATCH functions

Zhanshan Dong

Function review:

INDEX is used to find value in an array in a given location (column and row).

MATCH is used to find the position in an array by searching a given value in
the array.

We can use these two functions simultaneously to look up value in a two way table.

Suppose the table is in range A1:B10, and the value to be looked up is in A15,
use can use the following formula to get value if Column B corresponding value
in A15 (in Column A).

= INDEX($B$1:$B$10,MATCH(A15,$A$1:$A$10,0))

See a simple sample in the sampel spreadsheet.