MATCH

Returns the position of a search item in a single row or column table.

Syntax:

MATCH(searchitem, searchregion, matchtype)

searchitem is the value to be found within the single row or single column range searchregion.

If matchtype is 1 or omitted, searchregion is assumed to be sorted in ascending order. MATCH then returns the position of the largest value in searchregion that is less than or equal to searchitem.

If matchtype is -1, searchregion is assumed to be sorted in descending order. MATCH then returns the position of the smallest value in searchregion that is greater than or equal to searchitem.

If matchtype is 0, MATCH returns the position of the first exact match. searchregion may be unsorted. In this case searchitem may contain a regular expression which will be interpreted if regular expressions are selected.

If the search fails, MATCH returns the #N/A error.

Example:

MATCH(5, B1:B4, 1)

where cells B1, B2, B3, B4 contain 3, 4, 8 and 9, returns 2. The second value in B1:B4 is 4, which is the largest value that is less than or equal to 5.

MATCH(5, B1:B4, -1)

where cells B1, B2, B3, B4 contain 9, 8, 4 and 3, returns 2. The second value in B1:B4 is 8, which is the smallest value that is greater than or equal to 5.

MATCH(5, B1:B4, 0)

where cells B1, B2, B3, B4 contain 4, 7, 5 and 2, returns 3, as the value 5 is the third item in B1:B4.

MATCH("r.d", B1:B4, 0)

where cells B1, B2, B3, B4 contain blue, red, green and pink, returns 2 (if regular expressions are enabled). The regular expression "r.d" matches red.

Application:

Imagine you're a teacher and you have a list of students with their scores on a recent test. You need to quickly find out where a specific student is located in your list. The MATCH function is perfect for this.

Scenario: You have the following student data in a spreadsheet:

	Student Name	Score
	A	B
1	Alice	85
2	Bob	92
3	Charlie	78
4	David	95
5	Eve	88

You want to find the position (row number) of the student named "David" in your list.

Using the MATCH Function:

The MATCH function has the following syntax:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value: The value you want to find. In this case, it's "David".
lookup_array: The range of cells where you want to search. This would be the "Student Name" column (e.g., A2:A6).
match_type: Specifies how the match is performed.
- 0: Exact match. This is the most common and what you'll use here.
- 1: Finds the largest value less than or equal to the lookup_value. Requires the array to be sorted in ascending order.
- -1: Finds the smallest value greater than or equal to the lookup_value. Requires the array to be sorted in descending order.

Applying the function:

To find David's position, you would enter the following formula into an empty cell:

MATCH("David", A1:A5, 0)

Result:

The function will return the number 4.

Why the result is 4:

"Alice" is in the 1st position of the specified range (A1:A5).
"Bob" is in the 2nd position.
"Charlie" is in the 3rd position.
"David" is in the 4th position.

Result:

4