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 ‘

**” and ‘**

*Price***”**

*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

*was*

**lookup_array****C5:C9**. We also set the

**to**

*matching_criteria***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.**

**=MATCH(D11,D5:D9,1)**

Here, the **D5:D9 **cell range is the l**ookup_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-

**=MATCH(“Pants”, C5:C9,0)**

The **MATCH(“Pants”, C5:C9,0) **syntax takes the ** lookup_value** “

**” and searches in the**

*Pants*

*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 ”

**” 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.**

*Pa****📌**** 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

**as**

*lookup_array***B5:B9**for the text

**Pa***as the

**. Then the**

*lookup_value***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.

**=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**.

### 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

**was combined by multiplying the results of checking two criteria within their respective columns. You may wonder why we have used**

*lookup_array***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.

**=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.

But when you use a small letter in the ** lookup_value** then it returns

**. So we can say that this formula works accurately. See the below image.**

*#N/A*### 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.

**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.

**=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.

## 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.