6. MATCH and INDEX functions
MATCH Function
The MATCH function returns the position of the value in the 1st argument in the list given in the 2nd argument.
Syntax
MATCH(Lookup_value, Lookup_array, [Match_type])
Arguments
Lookup_value : lthe value to search in the list given in the 2nd argument Lookup_array.
Lookup_array : list of values where the value given in the 1st 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 2nd argument.
Result
| Match_type | Behaviour of the MATCH function |
| 1 or omitted | The values in the 2nd 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 2nd argument do not have to be in order. The MATCH function returns the position of the first value equivalent to the searched value. |
| -1 | The values in the 2nd 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 1st 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 1st 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