Index and Match

Excel spreadsheet file used in video: Right-click to download file

Match

Match will search a range of cell for a specified item and return the relative position in the range. Match takes 3 parameters:

• lookup_value: This is the item being looked up.
• lookup_array: This is the range of cells that are being searched for the item.
• type: (optional) If the type is 1 or left out, match returns the largest value that is less than or equal to the lookup value. The lookup array must be in ascending order. If the type is set to 0 the lookup array is unsorted and the value returned is an exact match to the lookup value. If the type is set to -1 the smallest value that is greater than or equal to the lookup value. the lookup array must be in descending order.

Match is not case sensitive when looking up text. if type is set to 0 and an exact match is not found, an #N/A error is returned. In the following example Match is used to find the relative position of an item in a table of products:

The mixer is the 5th item down in the list of products.
The first parameter is the item being looked up in E2.
The next parameter is the lookup array which is the cell range A2 to A8
The 3rd parameter is 0. An exact match is sought.
Match can be used with other functions that use an index for lookup. For example VLOOKUP in it's 3rd parameter. In the following example VLOOKUP uses match to find the brand in column 2. By making the cell references to the product and the cell ranges in the table absolute, the formula can be copied to the price.

Index

Index has 2 different forms an array form and a reference form. The Array form used with match can return the value contained in any cell of a table. The Array form of Index takes 3 arguments. if the 2nd argument is omitted the 3rd is required and vice-versa.

• array: A range of cells
• row_num: index of row from which to return a value
• column_num: index of a column from which to return a value.

Index used with row_num parameter only:

Index used with column_num parameter only:

If both are used index returns the value in the cell at the intersection of row_num and column_num.

When Index is used with Match it can return the value of any cell in a table: