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

Match function in Excel There are quite a few practical applications. Here are the details How to use Match Excel function.

The Match function is a popular function in Excel functionsis used quite 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 to learn How to use 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.

Have 3 search types in the Match function on 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 wildcard characters “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 equal 0 and lookup_value in text format.
• Lookup_value There may be wildcard characters such as asterisks and question marks if match_type To be 0 and lookup_value is text. 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.

Combine Index and Match in Excel 3. Enter MATCH and pressTab

. 4. Select G2 as lookup value, B3:B13 is the source data and 0

Select lookup value 5. Press Enter

to fetch information for the selected application. 6. Follow the steps above and replace the power source INDEX equal D3:D13 to takeProfit

Apply match excel function formula to match data

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

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

7. Now this formula will be implemented in Excel:

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 cell Revenue and click tab Data

on the ribbon. 2. Click Data Validation and inAllow selectList

Select List 3. Select the column header Revenue and Profit To beSource

. 4. ClickOK

Index function formula in Excel

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

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

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.

Match function example

Example 1:

Value lookup table

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 as=MATCH(64,C2:C6,1)

Search type 1 in Match 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 2nd place

Search results for Match function type 1

Case 2: Search type is 0 Look for the location of value 70 in the data table. We will have the input formula as =MATCH(70,C2:C6,0)

Search type 0 in the Match function

Search results of type 0 in the Match function

Case 3: Search type is -1 We will have the formula as =MATCH(65,C2:C6,-1)

Search type -1 in the Match function

Report an error in the Match function

Example 2:

Sort order in table The formula for searching order is =MATCH(D2,$D$6:$D$8,0)

The Match function searches for locations

Search results for ordinal position

• 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. [optional] match_type –
• 1 = (default) next exact or smallest value, 0 = exact match, -1 = next largest or exact value. Recipe: [match_type]=MATCH(lookup_value, lookup_array,

)

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 QuanTriMang Excel function

in the following articles!