Excel Match Function: Usage and Examples

The Excel Match function searches for a specified item in a defined range and returns its relative position in that range.

Function Syntax and Usage

Syntax: =MATCH(lookup_value, lookup_array, [match_type]).

In which:

- MATCH: is the function name.

- lookup_value: is the value to search for, which can be text or a number.

- lookup_array: is the array to search.

- [match_type]: is the match type. This parameter is optional. If you do not enter a value, Excel will default to match type = 1.

+ If you enter match type = 1: it finds the largest value that is less than or equal to the lookup value you specify above.

+ If you enter match type = 0: it finds an exact match with the lookup value.

+ If you enter match type = -1: it finds the smallest value that is greater than or equal to the lookup value you specify above.

Note:

• If you use match_type =1 , the search array must be sorted in ascending order, for example: a,b,c,etc; -2,0,3,4,etc.
• If you use match_type = -1 , the search array must be sorted in descending order.
• If lookup_value is a string, it must be enclosed in double quotes ''.
• The Match function is not case-sensitive.
• If the Match function does not find a matching value, it will return an error value #N/A.
• If match_type = 0 and lookup_value is a text string, you can use the question mark (?) to represent any single character and the asterisk (*) to match any sequence of characters. If you want to find a literal question mark or asterisk, type a tilde (~) before that character.
• If there are multiple values of lookup_value in the lookup_array, the result will return the smallest result.

Example

In the example above, lookup_value is a string, so it must be enclosed in double quotes ''. Otherwise, the return value will be #NAME?

If the search array lookup_array contains the same value as lookup_value (Hai Duong branch), the function will return the first value it encounters.

Use the Match function with case sensitivity

Formula: =MATCH(TRUE, EXACT(lookup_array, lookup_value, [match_type]) then press Shift + Ctrl + Enter instead of just Enter as usual.

The Exact function will help us compare the value to find (lookup_value) with each value in the search area (lookup_array). If the compared cell does not match 100% with the value to find, the function will return False until the return value is True (the compared cell matches 100% with the value to find). And then the Match function will check the position of the True value in the search area (lookup_array).

You can refer to the following example to see the difference between using the Match function (1) and using the Match function combined with Exact (2).

Combine MATCH with Lookup search function

You can use the Match function to get the relative position of the column/row you want to return, and provide the column/row number for the Row_index_number parameter for the Hlookup function / Col_index_number parameter for the Vlookup function.

Here is an example combining Vlookup and Match

In this case, the Match function finds the position of 'Quarterly Revenue' in the range A2 to E2, and returns the value 2 for the Col­_index_number parameter for the Vlookup function.

And another example for the combination of Hlookup and Match

In this case, the Match function finds the position of 'Hanoi Branch' in the range A2 to A6, and returns the value of row number 2 for the Row­_index_number parameter for the Hlookup function.

Above are the software tricks that have guided you on how to use the MATCH function and some of its basic applications. Hopefully, this article will be helpful to you.

Wishing you all success!