The Match function (the function searches for a specified value in an array or cell range) in Excel

The Match function is a fairly common function, applied a lot in the process of calculating and processing data on Excel spreadsheets. The Match function helps you search for a specified value in an array, a range of cells, and then returns the position of the value in the array or range.

The following article introduces the syntax and usage of the Match function in Excel.

The Match function (the function searches for a specified value in an array or cell range) in Excel Picture 1

Description

The MATCH function searches for a specified value in an array or range of cells, and the result is the relative position (or sequence number) of the value in that array or range.

Syntax

= MATCH (lookup_value, lookup_array, match_type)

Inside:

- lookup_value: the value you want to look for in the array (lookup_array), this value can be a number, text or logical value or a cell reference to a number, text or logical value.

- lookup_array: array, range of cells to be searched.

- match_type: search type, there are 3 types of search are -1, 0, 1.

1 or ignore (Less than): The MATCH function searches for the maximum value that is less than or equal to lookup_value. If this type of search is selected, lookup_array must be sorted in ascending order.

0 (Exact match): The MATCH function searches for the first value by exactly lookup_value and the values ​​in lookup_array can be sorted in any order.

-1 (Greater than): The MATCH function searches for the smallest value that is greater than or equal to lookup_value. Values ​​in lookup_array must be sorted in descending order.

Note

- The MATCH function returns the position of the search value in lookup_array, not the search value itself.

- Do not distinguish between uppercase and lowercase letters when looking for text values.

- If no lookup value is found in lookup_array, MATCH will return an error value.

- If match_type is 0 and the lookup_value search value is text, can the search value contain asterisks * (suitable for any string of characters) and a question mark? (matches any single character). And if you want to find a question mark or asterisk, type the tilde ~ before the character.

For example

For the data sheet:

The Match function (the function searches for a specified value in an array or cell range) in Excel Picture 2

The search type is 1 or omitted, for example, searching for position 64 in the Total column :

= MATCH (64, C6: C9,1)

Because the value 64 is not in the Total column, the function will return the position of the largest value that is less than 64 (63) and the result is as follows:

The Match function (the function searches for a specified value in an array or cell range) in Excel Picture 3

The search type is 0, for example, find the position of 65 in the Total column :

= MATCH (65, C6: C9,0)

The Match function (the function searches for a specified value in an array or cell range) in Excel Picture 4

The search type is -1, for example: = MATCH (65, C6: C9, -1) and the error results because arrays are not sorted in descending order.

The Match function (the function searches for a specified value in an array or cell range) in Excel Picture 5

The way to use the Match function is quite simple, with different processing and calculation requirements, you can combine the Match function with some other functions in Excel to process data more efficiently. Good luck!