The INDEX and MATCH Functions

The INDEX and MATCH functions are, in a sense, mirror images. The function INDEX(array, row num, column num, area num) returns a single value from within a one- or two-dimensional range of cells, based on a specified position in the array. Non-adjacent selections are permitted they are handled by area num. See Appendix B for details. 

The function MATCH lookup value, array, match type num) returns the relative position of a value in a one-dimensional array. If match type num = 1, MATCH returns the position of the largest array value that is less than or equal to lookup value. The array must be in ascending order. If match type num = -1, MATCH returns the position of the smallest value that is greater than or equal to lookup value. The array must be in descending order. If match type num = 0, MATCH returns the position of the first value that is equal to lookup value. The array can be in any order. If no match is found, N/A is returned. 

For an example of using VLOOKUP and MATCH, see Looking Up Values in Tables in Chapter 9. 

---