Match function in Excel - Usage and illustrative examples

The Match function in Excel finds a specified item in a specified range and, returns the relative position of that value in this range.

Match function in Excel - Usage and illustrative examples Picture 1

Function syntax and usage

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

Inside:

- MATCH : is the function name.

- lookup_value: is the value to search, be it text or numeric.

- lookup_array: is the search array.

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

+ If you type match = 1: is to find the largest value in values ​​that is less than or equal to the value you are looking for that you specified above.

+ If you type match = 0: is to find the correct value equal to the value to search.

+ If you type match = - 1: is to find the smallest value in the values ​​greater than or equal to the value you are looking for that you specified above.

Note:

1. 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.
2. If you use match_type = -1, the search array must be sorted in descending order.
3. If lookup_value is case sensitive, double quotes must be entered.
4. The Match function is not case sensitive.
5. If the Match function is not found, the result will return the # N / A error value .
6. If match_type = 0 and lookup_value are text strings, you can use the question mark character (?) To represent any single character and the asterisk (*) that matches any string. If you want to find a real question mark or asterisk, type a tilde (~) before the character.
7. If the lookup_array search array has more than one value of lookup_value, the result will return the smallest result.

For example

Match function in Excel - Usage and illustrative examples Picture 2

In the above example, lookup_value is in text form, so you must enclose the quotation marks "". Otherwise the return value will be #NAME?

The lookup_array search array has the same value as lookup_value (Hai Duong branch), the function will return the first value it meets.

Use the Match function, which is case sensitive

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

The Exact function will help us compare the lookup value ( lookup_value) with each value in the search area ( lookup_array ). If the compared cell does not match 100% of the value to be searched, the function will return False until the return value is True (the cell that matches 100% matches the value to be searched). 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 in combination with Exact (2).

Match function in Excel - Usage and illustrative examples Picture 3

Combine MATCH with the Lookup search function

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

You have the following example is a combination of Vlookup and Match rows

Match function in Excel - Usage and illustrative examples Picture 4

In particular, the Match function finds the position of "First Quarter Revenue" in the array A2 through E2, and returns the value 2 for the Col_index_number parameter for the Vlookup function.

And another example for a combination of Hlookup and Match rows

Match function in Excel - Usage and illustrative examples Picture 5

In particular, the Match function finds the position of "Hanoi Branch" in the array A2 through A6, and returns the row value 2 for the Row_index_number parameter for the Hlookup function.

Above software tips have shown you how to use the MATCH function and some of its basic applications. Hope this article will help you.

Good luck!