Excel INDEX and MATCH function
=INDEX() returns the value of a cell in a table based on the column and row number. =MATCH() returns the position of a cell in a row or column. Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.
The INDEX function the value of an element in a table or an array, selected by the row and column number indexes.
Use the array form if the first argument to INDEX is an array constant.
Syntax:
INDEX(array, row_num, [column_num])
The array form of the INDEX function has the following arguments:
array is required. A range of cells or an array constant.
If array contains only one row or column, the corresponding row_num or column_num argument is optional.
If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
row_num : Required, unless column_num is present. Selects the row in array from which to return a value. If row_num is omitted, column_num is required.
column_num: Optional. Selects the column in array from which to return a value. If column_num is omitted, row_num is required.
#indexmatch
MATCH function syntax has the following arguments:
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value is required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
lookup_array is required. The range of cells being searched.
match_type is optional. The number -1, 0, or 1.
The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
Match_type Behavior
1 or omitted
MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0
MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1
MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
#match
#matchfunction