# 6. MATCH and INDEX functions

## MATCH Function

The MATCH function returns the position of the value in the 1^{st} argument in the list given in the 2^{nd} argument.

### Syntax

*MATCH(Lookup_value, Lookup_array, [Match_type])*

#### Arguments

*Lookup_value* : lthe value to search in the list given in the 2^{nd} argument *Lookup_array*.*Lookup_array* : list of values where the value given in the 1^{st} argument *Lookup_value* is searched.*Match_type* : Optional argument, default value is 1. Possible values are -1, 0 or 1. This argument determines how the comparison will take place between the searched value and the values of the list given in the 2^{nd} argument.

#### Result

Match_type |
Behaviour of the MATCH function |

1 or omitted | The values in the 2^{nd} argument must be in ascending order. The MATCH function returns the position of the nearest equal or lesser value to the value being searched. |

0 |
The values in the 2 |

-1 | The values in the 2^{nd} argument must be in descending order. The MATCH function returns the position of the nearest greater than or equal to the value being searched. |

### Examples

Formula |
Result |
Notes |

=MATCH(5,{1,2,4,6},1) | 3 | Match_type=1. The closest equal or lesser value to 5 in the list is 4. The position of the value 4 in the list is 3. |

=MATCH(7,{3,7,7,1},0) | 2 | Match_type=0. The value 7 is in the list at positions 2 and 3. The MATCH function returns the first position 2. |

=MATCH(6,{3,7,5,1},0) | #N/A | Match_type=0. The value 6 is not in the list. The MATCH function therefore returns the error #N/A. |

=MATCH("a?",{"a","aaa","bb","ab"},0) | 4 | Match_type=0; The wildcard character ? corresponds to one and only one character. The corresponding value for "a?" is "ab" at position 4. |

=MATCH(5,{8,6,4,2},-1) | 2 | Match_type=-1. The closest equal or greater value to 5 in the list is 6. The position of the value 6 in the list is 2. |

## INDEX Function (Array form)

The INDEX function returns an element of a one- or two-dimensional array or a range of cells. The INDEX function can also return a part of an array.

### Syntax

*INDEX(array,row_num,column_num)*

#### Arguments

*array* : array or range of cells*row_num* : specifies the row number of the element to return from the array or range of cells specified in the 1^{st} argument *array*. This argument is optional if *array* contains a single row.*column_num* : specifies the column number of the element to return from the array or range of cells specified in the 1^{st} argument *array*. This argument is optional if *array* contains a single column.

#### Result

The INDEX function returns the *array* element located at the intersection of the row with number *row_num* and the column with number *column_num*.

If *row_num* is 0, then the column with number *column_num* is returned entirely.

If *column_num* is 0, then the row with number *row_num* is returned entirely.

### Examples

Consider the following example:

In cell B12 the formula is

=INDEX(A1:J10,5,4)

This is to have the element at the intersection of row 5 and column 4 of the range A1:J10.

*Example with **column_num*=0

To have the elements from row 6 at row 15 as in the example above, do the following:

- Select the cell range A15:J15
- Write the formula

=INDEX(A1:J10,6,0)

- Confirm with the key combination
**Ctrl+Shift+Enter**. This formula returns an array of elements, so to display it on the range A15:J15, you must validate with the key combination**Ctrl+Shift+Enter**.

NB. If you click on any cell in the range A15:J15, the formula is displayed in the formula bar between square brackets { }.

*Example with **row_num*=0

Similarly, if we want to have for example the elements of column 4 in column L :

- Select the cell range L1:J10
- Write the formula

=INDEX(A1:J10,0,4)

- Confirm with the key combination
**Ctrl+Shift+Enter**.

*Example with a single column cell range*

If the range of cells given as the first argument is in a single column, then you can ignore the third argument *column_num*.

For example, to get the value in the third row of column G, the formula is :

=INDEX(G1:G10,3)

*Example with a single row cell range*

If the range of cells given as the first argument is on a single row, then you can ignore the second argument *row_num*.

For example, to get the value in the sixth column of row 5, the formula is :

=INDEX(A5:J5,,6)

You can even write it :

=INDEX(A5:J5,6)

NB. Another version of the INDEX function called "Reference Form" is available in Excel 2016. With this version, you can specify several references to cell ranges as the first argument. In the fourth argument, the number of the range to be used for the search is specified.

NB. The same problems for which we use the functions VLOOKUP and HLOOKUP can also be solved using the functions EQUIV and INDEX.

## Exercise

Exercise – MATCH and INDEX Functions