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.
In this article, you will also find some articles where you can learn how to use the MATCH function for different match types, with other Excel Functions and what to do when this function is not working.
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.
How to Use MATCH Function in Excel: 8 Practical Examples
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.
Read More: Advanced Excel Lookup Functions
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.
Read More: How to Use VLOOKUP and HLOOKUP Combined Formula in Excel
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.
Read More: DGET vs VLOOKUP in Excel
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.
Read More: How to Use LOOKUP Function in Excel
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.
Read More: How to Use HLOOKUP Function in Excel
3. Applying the 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. 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.
Read More: How to Use Excel HYPERLINK Function
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.
Read More: How to Use XLOOKUP Function in Excel
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.
Read More: How to Use FILTER Function in Excel
6. Applying the 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.
Read More: How to Use SORT Function in Excel
7. Use the MATCH Function with VLOOKUP in Excel
When you use the VLOOKUP function to look up a value from a dataset, if you delete or insert any column from that dataset, the function will not work.
In that case, you can use the MATCH function with VLOOKUP to do the task. Here, we used the following formula to look up the Sales value of a product.
=VLOOKUP(G4,$B$4:$D$9,MATCH($F$5,$B$4:$D$4,0),FALSE)
In the formula, we used the cell range B4:D9 as table_array. Now, if we delete the Quantity column. The formula will change the table_array to B4:C9 and show the result.
Read More: How to Use Excel UNIQUE Function
8. Apply HLOOKUP with the MATCH Function to Lookup Values in the Horizontal Dataset
Similarly, you can use the HLOOKUP function to look up values in a horizontal dataset. Use the following formula to do that.
=HLOOKUP(C8,B4:G6,MATCH(B9,B4:B6,0),FALSE)
Read More: How to Use Excel FORMULATEXT Function
Frequently Asked Questions
1. Is the MATCH function better than the VLOOKUP function?
No, the VLOOKUP function is better as it returns a value rather than the position of a value like the MATCH function. However, by combining these two functions you can look for a value from a dataset, and deleting any row or column will not change the result.
2. What does the MATCH function return if no match is found?
If the MATCH function doesn’t find a match, it returns the error value #N/A.
3. Can the MATCH function work with both rows and columns?
Yes, the MATCH function can be used to search for a value in either rows or columns. The lookup_array can be a single row or a single column.
4. Can the MATCH function figure out the difference between uppercase and lowercase values?
No, the MATCH function cannot figure out the difference between uppercase and lowercase values.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
Conclusion
That’s all about today’s session. And these are some easy methods for using the MATCH function in Excel. We have also provided some articles here to help you use this function with different match types and the ISNUMBER function. You can go through the article we have given here when the MATCH function is not working in your Excel worksheet. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Thanks for your patience in reading this article.
