Match()

The Match() function returns the relative position of an item in a single row or single column array that meets a specified value.

Syntax

MATCH(search criterion;lookup_array;type)

Search Criterion is the value which is to be searched for in the single-row or single-column array.

lookup_array is the reference searched. A lookup array can be a single row or column, or part of a single row or column.

type

• If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order.

• If Type = -1 it is assumed that the column in sorted in descending order.

• If Type = 0, only exact matches are found. If the search criterion is found more than once, the function returns the first one found. Only if Type = 0 can you search for regular expressions.

If Type = 1 or the third parameter is missing, the last value that is smaller or equal to the search criterion is returned. This applies even when the search array is not sorted. For Type = -1, the first value that is larger or equal is returned.

The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character.

For this example, please refer to the screen shot.

Cell A1 contains the following formula =MATCH("Jason";C2:C11;0)

Cell A3 contains the following formula =MATCH("Carey";C14:C23;1)

Cell A5 contains the following formula =MATCH("Carey";H14:H23;-1)

Cell A7 contains the following formula =MATCH("Carey";C2:C11;0)

Cell A9 contains the following formula =MATCH("Ca.*";C2:C11;0)

Screen shot. You click on the image for a larger view.

The first range on the spreadsheet is not sorted in any particular order so cells A1, A7, A9 has type set to 0.

The second range is sorted in ascending order so cell A3 has type set to 1.

The third range is sorted in descending order so cell A5 has type set to -1.

Notice the results of each tell you what row number the search criteria is in for that particular range, not what row it is in in the spreadsheet.

Also, when type is set to 1, the MATCH function returns the row number of the first time it finds the name. When type is set to -1 the MATCH function returns the row number of the second (or last) time it finds the name.

Cell A9 has search criteria "Ca.*". This looks for any match that begins with Ca. It does not matter what comes after Ca.