How to use MATCH function in Excel (3 Examples)

There are several lookup functions in Excel to perform various lookup and searching operations easily and swiftly. Today we are going to show you how to use a lookup function called: MATCH. For the session, we are using Excel 2019, feel free to choose yours (at least Excel 2003).

Overview MATCH function - Excel MATCH Function

Providing the lookup_value, lookup_array, and match_type you can find the position of the value within the array.

Practice Workbook

You are welcome to download the practice workbook from the link below.

MATCH Function

1. Basics of MATCH

The MATCH function in Excel is used to locate the position of a lookup value in a row, column, or table.

syntax-Excel MATCH Function

Summary

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

Syntax

MATCH(lookup_value,lookup_array,[match_type])

Arguments

Argument Required/Optional Description
lookup_value Required The value to match in the array
lookup_array Required A range of cells or an array reference where to find value
match_type Optional Specifies how Excel matches lookup_value with values in lookup_array.

1 = exact or next smallest

0 = exact match

-1 = exact or next largest

Versions

Workable from Excel 2003

2. Use of MATCH

I. Finding the Position of a Value

From the description of the MATCH function, you have understood that this function will help you locate the given lookup value from an array. Let’s see with examples:

Exact Match

To show you examples we have brought a dataset of several products with their respective prices.

Example sheet - Excel MATCH Function

We are going to set lookup_value in the Lookup Value field.

Criteria in Example - Excel MATCH Function

Here our lookup_value is Shirts. 

Since we are looking for an exact match, our formula will be

=MATCH(F3,B4:B8,0) 

We have used Cell Reference, lookup_value was in cell F3, and the lookup_array was B4:B8.

Write the formula in Excel.  

Result - Excel MATCH Function

Our formula produced the position of Shirts in the array.

You can change the lookup_value to see the position for that value.

Example 2 - Excel MATCH Function

Here our lookup_value was Pants and found the position for this using the formula.

Approximate Match

We can locate by approximate match. In most cases, the approximate match is used for numbers. So to keep things simple we are going to set numbers as our lookup_value.

Approximate match - Excel MATCH Function

Here 535 is our lookup_value. You can see there is no value equal to 535 in our dataset.

Our formula will be

=MATCH(F3,C4:C8,1)

C4:C8 is the lookup_array here. Since approximate is our ultimate target we have chosen 1 in our match_type field. Approximate match result - Excel MATCH Function

1 returns the nearest smallest value of the lookup_value. Here 500 was the nearest value to the 535. And our formula returned the position of 500.

Let’s change the lookup_value.

Example 2 Approximate match - Excel MATCH Function

We have set 335 as lookup_value and the formula provided the nearest small value (300) position.

II. Find value corresponding to another value

We can find a value corresponding to another value. We need to use another function called INDEX along with the MATCH function.

The INDEX function returns the value at a given location in a range or array. To know more about the function, visit this INDEX function article.

For showing you examples we are set to find the price for a product (lookup_value)

Find value - Excel MATCH Function

Here our lookup value is Shirts. Let’s find the price for this product.

We need to write the MATCH function inside INDEX. Our formula will be

=INDEX(C4:C8,MATCH(F3,B4:B8,0)) 

C4:C8 is the array where we need to find the value. Using the MATCH function, we have set the row_number. 

INDEX-MATCH - Excel MATCH Function

You have seen how MATCH provides the position. The MATCH portion here provided 1.

Then from the array (C4:C8), the INDEX function returned the value of the position of row 1.

Let’s change the lookup_value.

INDEX-MATCH 2- Excel MATCH Function

Our lookup_value was Pants and the formula provided the price of Pants. 

III. MATCH in Array Formula

We can use the MATCH function in an array formula.

Let’s imagine a scenario where we have products with size and price.

Array formula data - Excel MATCH Function

We will find the price using the product name and size.

Criteria in array formula - Excel MATCH Function

Here we have chosen our criteria as Shirts and XL size.

We need to use the INDEX function since we are aiming to find value. Our formula will be

=INDEX(D4:D9,MATCH(1,(G3=B4:B9)*(G4=C4:C9),0))

Here we have used 1 as lookup_value in MATCH. And the lookup_array was combined by multiplication of checking two criteria within their respective columns.

Result Array formula - Excel MATCH Function

You may wonder why we have used 1 as the lookup_value, let’s help you to understand.

The (G3=B4:B9) and (G4=C4:C9) provide an array of TRUE or FALSE. Multiplying the arrays another array of TRUE and FALSE formed. TRUE can be represented as 1. So we are looking for the TRUE value inside the array.

You can see our formula provided the value that we were looking for. Since it is an array formula you need to press CTRL + SHIFT + ENTER to execute it.

Array formula Example - Excel MATCH Function

Now we have changed the criteria value and found the desired result.

3. Quick Notes

  1. Apart from the Cell Reference, we can insert the lookup_value directly into the function.

Direct input - Excel MATCH Function

Here we have set “Pants” directly. Press ENTER to execute the formula.

Result direct input - Excel MATCH Function

We have found the result as expected (the price of Pants).

  1. Wildcards can be used within the MATCH To set wild card we need to use the "*" symbol.

Wildcard - Excel MATCH Function

Here we have set “Pa*” as our lookup_value. This denotes that value will be any value that starts with Pa and then can be any value of any number of characters.

Wildcard result - Excel MATCH Function

We have found the price for the Pants. This starts with “Pa” and then 3 characters “nts”. 

We can use the "?" symbol as the wildcard as well.

Wildcard 2-Excel MATCH Function

But there is a problem, "?" denotes only a single character. Here we have used a single "?" sign, so this will look for a value of three characters starting with “Pa”.

Wildcard result 2 Excel MATCH Function

Since there is no value listed as such (starts with Pa and three characters) the formula provided an error.

  1. For the approximate match, we can use -1, this will provide the nearest large value of the lookup_value.

Approximate match -1 - Excel MATCH Function

Here for the value 335, we have used -1 in the match_field. It provided the #N/A error. Because the data should be in descending order prior to using -1 as match type.

Let’s sort the data in descending order.

Approximate match -1 - Excel MATCH Function

The data is sorted in descending order and the formula works. We have found the position for the nearest larger value of 335.

Conclusion

That’s all for today. We have tried showing how you can use the MATCH function. You can use the function solo or with the combination of any other function to locate the position or value. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your MATCH function-related scenarios where you have stuck, we are ready to help.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo