Index and Match

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

Match

Match will search a row or column for an item and return the index number.
Match takes 3 parameters:

• Search Criterion: this is the item being searched for.
• Lookup_array: this is the range of cells being searched.
• Type: (optional) If set to 0 only exact matches are found. If set to 1 or the parameter is left out the range is sorted in ascending order, the last value that is smaller or equal to the search criterion is returned. If set to -1 the range is sorted in descending order, the last value that is larger or equal to the search criterion is returned.

For example In a search for an item in a column of products MATCH is used to find the index of the item:

Here a range A2 to A8 was seached and MATCH returned the index of the second item down, the Blender.
MATCH can be used with other functions that use an index in their parameters rather than typing them in. The VLOOKUP uses an index for its 3rd parameter:

By using MATCH for the index the formula can be copied to other columns.
The Index function will take a row or column index and return the value in the cell location. Index has 4 parameters:

• Reference:the range of cells that contain the item being searched.
• Row: (optional) Index of the row for the reference range.
• Column: (optional) Index of the column for the reference range.
• Range: (optional) Index of the subrange if referring to a multiple range.

In the following example INDEX returns the second item in the range A2:A8 the blender:

Contrast with MATCH which returns the index number of the row.
MATCH can be combined with INDEX to find the value in any cell location in a table. In this example match is used to find the model number in the first column:

The formula was placed in B11.
The column with model numbers is put into the first parameter, then MATCH is placed in the second parameter, it's first parameter refers to the Toaster Oven in A11, a reference to the product column is placed in its second parameter, so it searches for the toaster oven in the second column, finds the index number, which INDEX uses to return the model number in column A on the same row.