There are several lookup functions in Excel to perform various lookup and searching operations easily and swiftly. Today we are going to show you how to use a lookup function called: **MATCH**. For the session, we are using Excel 2019, feel free to choose yours (at least Excel 2003).

Providing the lookup_value, lookup_array, and match_type you can find the position of the value within the array.

**Table of Contents**hide

## Practice Workbook

You are welcome to download the practice workbook from the link below.

## Excel MATCH Function

### 1. Basics of MATCH Function

The **MATCH** function in Excel is used to locate the position of a lookup value in a row, column, or table.

#### Summary

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

Argument |
Required/Optional |
Description |
---|---|---|

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.
1 = exact or next smallest 0 = exact match -1 = exact or next largest |

#### Versions

Workable from Excel 2003

### 2. Use of MATCH Function

#### I. 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 with examples:

**Exact Match**

To show you examples we have brought a dataset of several products with their respective prices.

We are going to set *lookup_value* in the *Lookup Value *field.

Here our *lookup_value* is *Shirts. *

Since we are looking for an exact match, our formula will be

`=MATCH(F3,B4:B8,0) `

We have used **Cell Reference, **lookup_value was in cell **F3, **and the lookup_array was **B4:B8.**

Write the formula in Excel. ** **

Our formula produced the position of *Shirts *in the array.

You can change the *lookup_value* to see the position for that value.

Here our *lookup_value* was *Pants *and found the position for this using the formula.

**Approximate Match**

We can locate by approximate match. In most cases, the approximate match is used for numbers. So to keep things simple we are going to set numbers as our *lookup_value*.

Here 535 is our *lookup_value*. You can see there is no value equal to 535 in our dataset.

Our formula will be

`=MATCH(F3,C4:C8,1)`

**C4:C8 **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 500 was the nearest value to the 535. And our formula returned the position of 500.

Let’s change the *lookup_value*.

We have set 335 as *lookup_value* and the formula provided the nearest small value (300) position.

#### II. Find 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. To know more about the function, visit this **INDEX** function article.

For showing you examples we are set to find the price for a product (lookup_value)

Here our lookup value is *Shirts. *Let’s find the price for this product.

We need to write the **MATCH **function inside **INDEX.** Our formula will be

`=INDEX(C4:C8,MATCH(F3,B4:B8,0)) `

**C4:C8 **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 here provided 1.

Then from the array (**C4:C8**), the **INDEX **function returned the value of the position of row 1.

Let’s change the *lookup_value*.

Our *lookup_value* was *Pants* and the formula provided the price of *Pants. *

#### III. MATCH in Array Formula

We can use the **MATCH **function in an array formula.

Let’s imagine a scenario where we have products with size and price.

We will find the price using the product name and size.

Here we have chosen our criteria as *Shirts *and *XL *size.

We need to use the **INDEX **function since we are aiming to find value. Our formula will be

`=INDEX(D4:D9,MATCH(1,(G3=B4:B9)*(G4=C4:C9),0))`

Here we have used 1 as lookup_value in **MATCH. **And the *lookup_array* was combined by multiplication of checking two criteria within their respective columns.

You may wonder why we have used 1 as the *lookup_value, *let’s help you to understand.

The **(G3=B4:B9) **and **(G4=C4:C9) **provide an array of **TRUE** or **FALSE**. Multiplying the arrays another array of **TRUE** and **FALSE** formed. **TRUE** can be represented as 1. So we are looking for the **TRUE **value inside the array.

You can see our formula provided the value that we were looking for. Since it is an array formula you need to press **CTRL + SHIFT + ENTER **to execute it.

Now we have changed the criteria value and found the desired result.

### 3. Quick Notes

- Apart from the
**Cell Reference,**we can insert the*lookup_value*directly into the function.

Here we have set “Pants” directly. Press **ENTER **to execute the formula.

We have found the result as expected (the price of *Pants)*.

- Wildcards can be used within the
**MATCH**To set wild card we need to use the`"*"`

symbol.

Here we have set “Pa*” as our *lookup_value*. This denotes that value will be any value that starts with *Pa *and then can be any value of any number of characters.

We have found the price for the *Pants. *This starts with “Pa” and then 3 characters “*nts”. *

We can use the `"?"`

symbol as the wildcard as well.

But there is a problem, `"?"`

denotes only a single character. Here we have used a single `"?"`

sign, so this will look for a value of three characters starting with “Pa”.

Since there is no value listed as such (starts with Pa and three characters) the formula provided an error.

- For the approximate match, we can use -1, this will provide the nearest large value of the
*lookup_value*.

Here for the value 335, we have used -1 in the match_field. It provided the **#N/A **error. Because the data should be in ** descending order** prior to using -1 as match type.

Let’s sort the data in descending order.

**Read More: **How to Use SORT Function in Excel (4 Examples)

The data is sorted in descending order and the formula works. We have found the position for the nearest larger value of 335.

## Conclusion

That’s all for today. We have tried showing how you can use the **MATCH** function. You can use the function solo or with the combination of any other function to locate the position or value. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your **MATCH** function-related scenarios where you have stuck, we are ready to help.

## Further Readings

- How to use XLOOKUP function in Excel (7 Examples)
- How to Use HLOOKUP Function in Excel (8 Suitable Approaches)
- How to Use VLOOKUP Function in Excel (2 Examples)
- How to Use FILTER Function in Excel (9 Easy Examples)
- VLOOKUP and HLOOKUP combined Excel formula (with example)
- Using Excel to Lookup Partial Text Match [2 Easy Ways]
- How to Find Duplicate Values in Excel using VLOOKUP