Excel Match Function

Excel Match function is categorized under excel lookup and reference functions. It is used to fetch the value in an array and returns the position of the value within the array. For example, we need to match the value of 5 in a range comprising(1, 5, 6, 8, 9). The function will return 2, as 5 is the 2^nd item in the given range.

It is a built-in excel function and can be used as a worksheet function.

Syntax

=match (lookup_value, lookup_array, [match_type])

Arguments

• Lookup_value. REQUIRED. Value that we want to look up. Numeric/ text/ logical value/ cell reference. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
• Lookup_array. REQUIRED. Data array to search in.
• Match_type. OPTIONAL. Can be set to 1, 0, and -1. By default, match function takes 1 as match type.

Match_type:

• 1 It returns the largest value that is less than or equals to the lookup value. It requires sorting of lookup array in ascending order, from smallest to largest; a to z.
• 0 Finds the first value in the array that exactly matches with the lookup value. No sorting is required.
• -1 returns the smallest value that is greater than or equals to the lookup value. Lookup array should be sorted in descending order; from largest to smallest; z to a.

Purpose

To find the relative position of lookup_value in a row, column, table or an array. For partial matches, wildcard characters are used.

Return Type

Integer

Return Value

Position of the lookup_value.

Remarks

• If match function does not find a match, it returns #N/A error.
• Match Function is not case-sensitive.
• If match_type = 0 and lookup_value is a text value, you can use wildcards in the lookup_value.

1. * is used to match any sequence of characters
2. ? is used to match any single character

• Use wildcards when match_type is set to 0.
• If lookup_value has several occurrences in the lookup_array, it returns the position of the first value.
• In financial analysis, match function is used in combination with other functions to lookup and return the values in the column. Commonly it is used with the INDEX function, generally called INDEX MATCH combination. Match returns a position, to fetch the value, learn using Index and Match together.

Sources:

• https://www.techonthenet.com/excel/formulas/match.php
• https://exceljet.net/excel-functions/excel-match-function
• https://support.office.com/en-us/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a