# Match function in Excel: How to use the Match function with examples

**The Match function in Excel** has many practical applications. Below are details on **how to use the Match Excel function** .

The Match function is a popular function in Excel functions, used a lot when processing Excel data tables and calculations. In a data table, when you want to search for a certain value in an array or cell range, the Match function will return the correct position of that value in the array or within the range of the data table. .

This helps users quickly find the value they need, without having to search manually, especially with tables with lots of data, which would be time-consuming. The article below will guide you **how to use the Match function in Excel** .

## Match function syntax in Excel

The syntax of the Match function in Excel is: **=Match(Lookup_value,Lookup_array,[Match_type]).**

In there:

- Lookup_value: search value in Lookup_array array. This value can be a number, text, logical value, or a cell reference to a number, text, or logical value, which is required.
- Lookup_array: array or cell range to be searched, required.
- Match_type: search type, not required.

There are **3 types of searches in the Match function** in Excel:

- 1 or omitted (Less than): the Match function searches for the largest value that is less than or equal to lookup_value. If the user selects this type of search, the lookup_array must be sorted in ascending order.
- 0 (Exact Match): the Match function will search for the first value that is exactly equal to lookup_value. The values in lookup_array can be sorted by any value.
- -1 (Greater than): Match function searches for the smallest value that is greater than or equal to lookup_value. Values in lookup_array must be sorted in descending order.

**Note when using the Match function:**

- The Match function returns the position of the lookup value in lookup_array, not the lookup value itself.
- You can use uppercase or lowercase letters while searching for text values.
- When the search value cannot be found in lookup_array, the Match function will report a search value error.
- In case Match_type is 0 and the lookup_value search value is text, the search value can contain * characters (for character strings) and question marks (for single characters). If you want to find a question mark or asterisk, type a tilde before that character.
- If nothing is entered, the default Match function is 1.

## Types of MATCH functions in Excel

Here are the different types of MATCH functions in Excel:

### 1. Exact match

The MATCH function performs an exact match when the match type is set to 0. In the example below, the formula in E3 is:

`=MATCH(E2,B3:B10,0)`

The MATCH function returns an exact match of 4.

### 2. Approximate match

MATCH will perform an approximate match on values sorted from AZ when the match type is set to 1, finding the largest value that is less than or equal to the lookup value. In the example below, the formula in E3 is:

The MATCH function in Excel returns an approximate match of 7.

### 3. Wildcard matching

The MATCH function can perform wildcard matching when the match type is set to 0. In the example below, the formula in E3 is:

The MATCH function returns the result of the wildcard character **'pq'.**

Note:

- The MATCH function is not case sensitive.
- Match returns the #N/A error if no match was found.
- The lookup_array argument must be in descending order: True, False, ZA,…9,8,7,6,5,4,3,…, etc.
- You can find wildcard characters like asterisks and question marks in
**lookup_value**if**match_type**is**0**and**lookup_value**is in text format. **Lookup_value**can have wildcards such as asterisks and question marks if**match_type**is**0**and**lookup_value**is text. The asterisk (*) matches any type of character string; Any single character is matched by a question mark (?).

### 4. How to apply Index and Match in an Excel formula

Match and Index when combined together will give you many benefits. For example, calculate revenue and profit from any application from the provided database. Here's how you can do this:

1. Open the INDEX function.

2. Select cells C3:C13 as the revenue data source (Revenue).

3. Enter **MATCH** and press **Tab** .

4. Select **G2** as the lookup value, **B3:B13** as the source data, and **0** for a complete match.

5. Press **Enter** to fetch information for the selected application.

6. Follow the steps above and replace the **INDEX** source with **D3:D13** to get **Profit** .

7. Now this formula will be implemented in Excel:

`=INDEX(C3:C13,MATCH(G2,B3:B13,0)) =INDEX(D3:D13,MATCH(G2,B3:B13,0))`

Above is an example of one-way use of INDEX MATCH in Excel. You can also select rows and columns to perform more complex searches.

For example, you can use a drop-down menu instead of separating cells for Revenue and Profit. You can follow these steps to practice:

1. Select the **Revenue** box and click the **Data** tab on the ribbon.

2. Click **Data Validation** and under **Allow** , select **List** .

**3. Select the Revenue** and **Profit** column heading as **Source** .

4. Click **OK** .

5. In this formula, you use the MATCH function twice to fetch both row and column values for the final INDEX function.

6. Copy & paste the following formula next to the Revenue cell to get the value. Just use the drop-down menu to choose between Revenue and Profit.

`=INDEX(C3:D13,MATCH(G2,B3:B13,0),MATCH(F4,C2:D2,0))`

## Match function example

**Example 1:**

We will take an example with the total number of products table below.

**Case 1: Search type is 1 or omitted**

Search for position number 61 in the Total column in the data table, meaning search for a value smaller than the search value. We enter the formula **=MATCH(64,C2:C6,1)** .

Because the value 64 is not in the Total column, the function will return the position of the nearest small value whose value is less than 64, which is 63. The result will return the value **in the 2nd position** in the column.

**Case 2: Search type is 0**

Look for the location of value 70 in the data table. We will have the input formula **=MATCH(70,C2:C6,0)** and then press Enter.

The returned result will be the position of value 70 in the Total column as the 4th position.

**Case 3: Search type is -1**

We will have the formula **=MATCH(65,C2:C6,-1)** as shown below.

However, because the array is not sorted in descending order, an error will be reported as shown below.

**Example 2:**

Given the student group data table below. Find the student's class order in this data table, with the order given below.

The order search formula is **=MATCH(D2,$D$6:$D$8,0)** then press Enter.

Immediately after that, the returned results will be the exact order of students by class, arranged according to the given rules.

In summary, things you need to remember when using the Match function in Excel:

- Purpose: Determine the position of any item in an array.
- Return value: A number representing a position in lookup_array.
- Argument:
- Lookup_value: Lookup value in the array.
- Lookup_array: Cell range or array reference.
- Match_type - [optional] 1 = (default) next exact or smallest value, 0 = exact match, -1 = next largest or exact value.

- Formula:
**=MATCH(lookup_value, lookup_array, [match_type])**

Above is everything you need to know about the MATCH function in Excel. Even though it's just a function to find values, not calculate values, it is extremely useful when you need to arrange data reasonably and logically.

Basically, using the Match function in Microsoft Excel is not too difficult if you understand all the basic information above. Besides the Match function, Excel has many other useful functions. Let's continue to learn about **Excel functions** with QuanTriMang in the following articles!

Wishing you success!

## You should read it

- How to use the Match function in Excel
- Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH
- Match function in Excel - Usage and illustrative examples
- How to combine Index and Match functions in Excel
- The Match function (the function searches for a specified value in an array or cell range) in Excel
- Basic Excel functions that anyone must know
- How to use the MATCH function and combine the MATCH function with the INDEX function
- How to use Hlookup function on Excel
- How to use the SUM function to calculate totals in Excel
- How to use the LEN function in Excel
- How to use COUNTIF function on Excel
- How to use the MOD function and QUOTIENT function in Excel

## Maybe you are interested

### Instructions to fix Excel files with format and extension don't match errors

### Bug fix: Voice match cannot be enabled

### 11 best free word matching games on Android and iPhone to improve English vocabulary

### Apple Watch can change color to match the clothes you wear in the future

### The curse of Kevin de Bruyne in decisive matches

### The Art of Brevity: How to Write Effective Push Notifications to Match Limits and Increase Engagement