Excel MATCH Function

What is MATCH Function in Excel?

The MATCH function is one of the Lookup & reference functions of Excel.

It returns the relative position of an item in an array that matches a specified value in a specified order.

We can find this function in Lookup & reference of insert function Tab.

How to use MATCH function in excel

1.Click on an empty cell (like F5).

2. Click on the fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select Lookup & reference category.

5. Select MATCH function.

6. Then select ok.

7. In the function arguments Tab, you will see MATCH function.

8. Lookup_value is the value you use to find the value you want in the array, a number, text, or logical value, or a reference to one of these.

9. Lookup_array is a contiguous range of cells containing possible lookup values, an array of values, or a reference to an array.

10. Match_type is a number 1, 0, or -1 indicating which value to return.

11. You will see the result in formula result section.

Examples of MATCH function in excel

Example 1:

Match type in match function

When match type is set to 1,MATCH finds the first value equal to lookup value. The lookup array does not need to be sorted.

=MATCH(28,B2:B10,0)----->>>>answer is  2

MATCH finds the largest value less than or equal to lookup value. The lookup array must be sorted in ascending order.

=MATCH(28,B2:B10,1)----->>>>answer is  6

MATCH finds the smallest value greater than or equal to lookup value. The lookup array must be sorted in descending order.

=MATCH(28,B2:B10,-1)----->>>>answer is  3

Example 2:

Default Setting of Match type in match function

Default Setting of Match type is 1. If you need an exact match, make sure you set the match type to zero. Otherwise, you will get the wrong result.

=MATCH(28,B2:B10)----->>>>answer is  wrong result

Example 3:

ٍExact Match in match function

ٍExact Match in match function means the match type is set to zero.

=MATCH("Oliver",A2:A10,0)----->>>>answer is 1

Example 4:

ٍApproximate Match in match function

ٍApproximate Match in match function means the match type is set to 1(ascending) or -1(descending).

=MATCH(28,B2:B10,1)----->>>>answer is  6

=MATCH(28,B2:B10,-1)----->>>>answer is  3

Example 5:

ٍWildcard Match in match function

When match type is set to zero (0), MATCH can perform a match using wildcards.

=MATCH("Eli*",A2:A10,0)----->>>>answer is  2

Example 6:

ٍCase-sensitive Match in match function (INDEX and EXACT)

Example 7:

Index Match for Complex Lookups

Example 8:

No match is found in MATCH Function

MATCH returns the #N/A error if no match is found.

=MATCH("Eli",A2:A10,0)----->>>>answer is  #N/A

MATCH related functions 

• INDEX Function
• VLOOKUP Function
• HLOOKUP Function
• LOOKUP Function
• XLOOKUP Function