The Match Function (the Function Searches for a Specified

This article provides a clear overview of The Match Function (the Function Searches for a Specified, with the main facts, useful context, and practical details organized for easy reading.

this guide introduces the syntax and usage of the Match function in Excel.

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)

Arguments

- lookup_value: the value you may need 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 may need to find a question mark or asterisk, type the tilde ~ before the character.

For Example

For the data sheet:

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 search type is 0, for example, find the position of 65 in the Total column:

= MATCH (65, C6: C9,0)

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 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!