Shakil Ahmed
Whenever you work on an Excel worksheet, you may want to find a relationship between two or more cells. Suppose you want to match criteria with other cells. In this case, you may use the MATCH function. Basically, it quickly and easily performs various lookups, searches for values, and returns the relative position of the lookup value in a number. If you want to know how to use the MATCH function, we are here to give real-life examples. In this article, we are going to show you seven examples based on different criteria for using the MATCH function in Excel. So, let’s get started.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
Introduction to MATCH Function in Excel
The MATCH function in Excel is used to locate the position of a lookup value in a row, column, or table and 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 Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| 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. Here, 1 = exact or next smallest, 0 = exact match and -1 = exact or next largest |
Return Value:
Returns the lookup value’s relative position.
Available Version:
Workable from Excel 2003.
6 Examples of Using the MATCH Function in Excel
To get to know the MATCH function pretty well, we have attached a dataset where we put some “Products” with the ‘Price” and ‘Serial Numbers”. Now, we find out the exact or approximate match for our search value.
Not to mention, we have used the “Microsoft 365” version. You can use any other version at your convenience.
1. 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 the examples.
1.1 Exact Match
The MATCH function can find the exact same match for your lookup_value. For the exact same match, simply select the value of the matching_criteria argument as 0.
📌 Steps:
- Firstly, go to cell C12 to enter the following formula.
We have used cell reference; the lookup_value was in cell D11, and the lookup_array was C5:C9. We also set the matching_criteria to 0 for the exact same match. The MATCH function returns the position of your value in cell D11.
1.2 Approximate Match
We can locate it based on an approximate match. In most cases, an approximate match is used for numbers. So to keep things simple, we are going to set numbers as our lookup_value. Look at the steps for better visualization.
📌 Steps:
- Initially, insert the below formula in cell D12.
Here, the D5:D9 cell range 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, 300 is the nearest value to 335. And our formula returned the position of 3.
1.3 Specific Text Match
The MATCH function can also take the text as its lookup value. We are trying to say that, if you want to find the value or position of a particular text in your dataset without knowing the cell reference, then you can put the text instead of the cell reference in the lookup_value. Please go through the formula we have described here.
📌 Steps:
- The formula we entered in cell D12 is-
The MATCH(“Pants”, C5:C9,0) syntax takes the lookup_value “Pants” and searches in the lookup_array C5:C9.
1.4 Wildcard Match
You can match the partial text and find out the position in the dataset. For example, you want to find out the position for the product “Pants“. In our formula, we have used the wildcard ” Pa*” instead of the full form to find the position of the text. The wildcard method is pretty cool, right? Follow the procedure to do it.
📌 Steps:
- Firstly, enter the below formula in cell C12.
Here, the MATCH function finds the exact match as we enter the matching_criteria as 0 in the lookup_array as B5:B9 for the text Pa* as the lookup_value. Then the INDEX function returns the value for the search result of the MATCH function. Here, the INDEX function first takes the result of the MATCH function and then finds the relation between the C5:C9 array and the Pa* text.
2. Finding a 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. Then the MATCH function checks for the match. Let’s jump into the formula.
📌 Steps:
- Firstly, in cell C12 insert the formula.
The B5:B9 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 is provided here at 2. Then, from the array B5:B9, the INDEX function returned the value of the position of row 2.
3. Applying MATCH Function in Array Formula
We can use the MATCH function in an array formula. We also need the INDEX function to display the result.
📌 Steps:
- Primarily, go to cell C14 and write up the formula.
Formula Explanation:
Here we have used 1 as the lookup_value in MATCH. And the lookup_array was combined by multiplying the results of checking two criteria within their respective columns. You may wonder why we have used 1 as the lookup_value; let’s help you understand.
The (C12=B5:B10) and (C13=C5:C10) provide an array of TRUE or FALSE. By multiplying the arrays, another array of TRUE and FALSE is formed. TRUE can be represented as 1. So we are looking for the TRUE value inside the array.
You can see that our formula provided the value that we were looking for. Then press ENTER to execute it. Since it is an array formula, you need to press CTRL + SHIFT + ENTER if you’re not a Microsoft 365 subscriber.
4. Utilizing Case-Sensitive MATCH Formula
For some case-sensitive text, you need to use the EXACT function and then the MATCH function to match the criteria. The structure of the formula used here is slightly different than that of the other MATCH function formula. Let’s see the example we have added here.
📌 Steps:
- We need to enter the following formula in cell D12 first.
Here, the EXACT(C5:C9, D11) syntax returns the exact same match for the lookup_array C5:C9, and the logical argument TRUE represents the existing value from the EXACT function.
But when you use a small letter in the lookup_value then it returns #N/A. So we can say that this formula works accurately. See the below image.
5. Comparing Two Columns Using ISNA and MATCH Functions
We have taken a dataset that we put into a list, and now we want to compare the 2nd list with the 1st one and display the values that don’t appear in the first list. Look at the dataset where we want to compare two columns using the ISNA and MATCH functions. We also use the IF function to display the logical result in text format.
📌 Steps:
- In the D5 cell enter the following formula.
Here, the MATCH function in Excel returns TRUE for the same match and FALSE for the not matching criteria. Then the ISNA function flips the results received from the MATCH function. Finally, the IF function returns the logical output as text.
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
6. Applying MATCH Function Between Two Columns
In this section, you can match between two columns. Suppose you have created a list of products that matches a previous column and want to take the value of “Price” that is exactly matched in our new column. To do this, we need to use the INDEX and MATCH functions together. Use the following formula.
📌 Steps:
- Firstly, move to F5 and input the formula.
This formula compares the text between columns B and E and returns the matching value.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Read More: Using Excel to Lookup Partial Text Match [2 Easy Ways]
Conclusion
That’s all about today’s session. And these are some easy methods for using the MATCH function in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, Exceldemy, a one-stop Excel solution provider, to explore diverse kinds of Excel methods. Thanks for your patience in reading this article.
