In this article, we are going to demonstrate various examples of using the **MATCH** function in Excel based on different criteria, and what to do when this function doesn’t work.

## 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 in which to lookup the value |

match_type | Optional | Specifies how Excel matches lookup_value with values in the lookup_array. 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:**

From Excel 2003 onwards.

## How to Use MATCH Function in Excel: 8 Practical Examples

To demonstrate the uses of the **Match function**, we’ll use the following dataset containing some “** Products**” with their ‘

**” and ‘**

*Price***” tofind out the exact or approximate match for our search value.**

*Serial Numbers*We used the Microsoft 365 version of Excel here, but the methods should work in any other version from Excel 2003 onwards.

### Example 1 – Finding the Position of a Value

#### 1.1 – Exact Match

For an exact match, simply set the ** matching_criteria** argument to

**0**.

**Steps:**

- In cell
**C12**, enter the following formula.

**=MATCH(D11, C5:C9,0)**

The ** lookup_value** is cell

**D11**, the

*is*

**lookup_array****C5:C9**, and the

**is**

*matching_criteria***0**for an exact same match. So this

**MATCH**function returns the position in the

*of the exact value in cell*

**lookup_array****D11**.

#### 1.2 – Approximate Match

In most cases, rather than an exact match, an approximate match is used for numbers.

**Steps:**

- Insert the below formula in cell
**D12**:

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

Here, the **D5:D9 **cell range is the **lookup_array**. Since an approximate match is our target, we set **1** in the **match_type** field, which returns the nearest smallest value to the **lookup_value**. **300** is the nearest value to **335**, so our formula returned the position of **3**.

#### 1.3 – Specific Text Match

**The MATCH function** can also take text as its lookup value, as opposed to the cell reference used above. This is useful if you want to find the value or position of particular text in your dataset without knowing the cell reference.

**Steps:**

- Enter the following formula in cell
**D12**:

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

The formulatakes the ** lookup_value** “

**” and searches for an exact match in the**

*Pants*

*lookup_array***C5:C9**.

#### 1.4 – Wildcard Match

You can also match text partially and return the position of the partial match in the dataset using a **wildcard**. For example, to find out the position for the product “** Pants**“, we can use the wildcard ”

**” instead of the full text.**

*Pa****Steps:**

- Enter the below formula in cell
**C12**:

**=INDEX(C5:C9, MATCH(“Pa*”, B5:B9,0))**

Here, **the MATCH function** finds an exact match (** matching_criteria** = 0) in the

**of**

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

**. Then**

*lookup_value*Pa***the INDEX function**takes the result of

**the MATCH function**and finds the relation between the

**C5:C9**array and the

**Pa***text.

### Example 2 – Finding a Value Corresponding to Another Value

We can also find a value corresponding to another value by using the **INDEX function** 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.

**Steps:**

- In cell
**C12**insert the following formula:

**=INDEX(C5:C9, MATCH(C11, B5:B9,0))**

**B5:B9 **is the array where we need to find the value. Using the **MATCH** function, we set the** row_number**,here

**2**. Then, from the array

**B5:B9**, the

**INDEX**function returns the value in the position of

**row 2**.

### Example 3 – Applying the MATCH Function in Array Formula

We also need **the INDEX function** to use** the MATCH function** in an array formula.

**Steps:**

- In cell
**C14**enter the following formula:

**=INDEX(D5:D10, MATCH(1,(C12=B5:B10)*(C13=C5:C10),0))**

Here we use **1** as the ** lookup_value** in

**MATCH**. The

**is combined by multiplying the results from checking two criteria within their respective columns.**

*lookup_array*The (**C12=B5:B10**) and (**C13=C5:C10**) criteria 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 position of the **TRUE** value inside the array.

Since this is an array formula, press **CTRL **+ **SHIFT **+ **ENTER** to execute it if you’re not a **Microsoft 365** subscriber (where just pressing **Enter** will suffice).

### Example 4 – Utilizing Case-Sensitive MATCH Formula

For case-sensitive text, use the EXACT function and then** the MATCH function** to match the criteria. The structure of the formula is slightly different from that of the **MATCH **function formulas used in the above examples.

**Steps:**

- Enter the following formula in cell
**D12**:

**=MATCH(TRUE, EXACT(C5:C9, D11),0)**

The **EXACT(C5:C9, D11) **syntax returns an exact match in the* lookup_array *

**C5:C9**, and the logical argument

**TRUE**represents the existing value from

**the EXACT function**.

But when we use a lowercase letter in the ** lookup_value** then it returns

**. So this formula works accurately, because an exact, case-sensitive match is required.**

*#N/A***Read More:** Excel MATCH Function Match Type

### Example 5 – Comparing Two Columns Using ISNA and MATCH Functions

Suppose we have a dataset of 2 lists, and we want to compare the 2nd list with the 1st one and return the values that don’t appear in the first list. We can do this using the ISNA and **MATCH** functions, along with the IF function to display the logical result in text format.

**Steps:**

- In cell
**D5**enter the following formula:

**=IF(ISNA(MATCH(C5, B5:B12,0)), “Not in List 1″,””)**

Here, **the MATCH function** in Excel returns **TRUE** for an exact match and **FALSE** otherwise. Then **the ISNA function** flips the results received from **the MATCH function**. Finally, **the IF function** returns the logical output as text.

### Example 6 – Applying the MATCH Function Between Two Columns

Suppose we have a list of products that match a previous column, and we want to put the value of “** Price**” that is an exact match in our new column. To do this, we need to use the

**INDEX**and

**MATCH**functions together.

**Steps:**

- In cell
**F5**enter the formula below:

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

### Example 7 – Use the MATCH Function with VLOOKUP in Excel

When using the VLOOKUP function to look up a value from a dataset, deleting or inserting any column from that dataset will break the function.

In that case, use **the MATCH function** with **VLOOKUP **to do the task. To look up the **Sales **value of a product, we can use the following formula:

`=VLOOKUP(G4,$B$4:$D$9,MATCH($F$5,$B$4:$D$4,0),FALSE)`

In the formula, we use 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.

### Example 8 – Apply HLOOKUP with the MATCH Function to Lookup Values in a Horizontal Dataset

Similarly, 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?**

The error value **#N/A**.

**3 – Can the MATCH function work with both rows and columns?**

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

**Download Practice Workbook**

Download the following practice workbook. It will help you to realize the topic more clearly.

Uses of MATCH Function.xlsx

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