Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use MATCH Function in Excel (7 Practical Examples)

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.

Quick view of MATCH function in Excel


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.

Match function outline in Excel

  • 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.
=MATCH(D11, C5:C9,0)

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.

Excel MATCH Function for exact match


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.
=MATCH(D11,D5:D9,1)

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.

Excel MATCH Function for approximate match


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-
=MATCH(“Pants”, C5:C9,0)

The MATCH(“Pants”, C5:C9,0) syntax takes the lookup_valuePants” and searches in the lookup_array C5:C9.

Specific Text Match function 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.
=INDEX(C5:C9, MATCH(“Pa*”, B5:B9,0))

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.

Wildcard Match


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.
=INDEX(C5:C9, MATCH(C11, B5:B9,0))

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.

Finding a Value Corresponding to Another Value for excel match function


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.
=INDEX(D5:D10, MATCH(1,(C12=B5:B10)*(C13=C5:C10),0))

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.

Applying MATCH Function in Array Formula in Excel


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.
=MATCH(TRUE, EXACT(C5:C9, D11),0)

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.

Utilizing Case-Sensitive MATCH Formula

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.
=IF(ISNA(MATCH(C5, B5:B12,0)), “Not in List 1″,””)

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.

Comparing Two Columns Using ISNA and MATCH Function


Similar Readings


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.
=INDEX($C$5:$C$12, MATCH(E5,$B$5:$B$12,0))

This formula compares the text between columns B and E and returns the matching value.

Applying MATCH Function Between Two Columns in EXcel


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


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.


Related Articles

Shakil Ahmed

Shakil Ahmed

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