Shakil Ahmed
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).
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.
Excel MATCH Function
1. Basics of MATCH Function
The MATCH function in Excel is used to locate the position of a lookup value in a row, column, or table.
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 Function
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.
We are going to set lookup_value in the Lookup Value field.
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.
Our formula produced the position of Shirts in the array.
You can change the lookup_value to see the position for that value.
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.
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. 
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.
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)
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.
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.
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.
We will find the price using the product name and size.
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.
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.
Now we have changed the criteria value and found the desired result.
Similar Readings
- How to Use Excel HYPERLINK Function (8 Examples)
- Use SORT Function in Excel (4 Examples)
- How to Find Duplicate Values in Excel using VLOOKUP
3. Quick Notes
- Apart from the Cell Reference, we can insert the lookup_value directly into the function.
Here we have set “Pants” directly. Press ENTER to execute the formula.
We have found the result as expected (the price of Pants).
- Wildcards can be used within the MATCH To set wild card we need to use the
"*"symbol.
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.
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.
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”.
Since there is no value listed as such (starts with Pa and three characters) the formula provided an error.
- For the approximate match, we can use -1, this will provide the nearest large value of the lookup_value.
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.
The data is sorted in descending order and the formula works. We have found the position for the nearest larger value of 335.
Read More: Using Excel to Lookup Partial Text Match [2 Easy Ways]
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.
