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 ‘

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

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

**=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**. 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***Read More:** Excel MATCH Function Match Type

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

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

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

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

### 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: **Excel MATCH Function Not Working

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

**<< Go Back to**** Excel Functions ****|**** Learn Excel**