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

Excel Choose Function

It is a built-in lookup/ reference function that is used to get the value using a giving position or index. Also, it can be used as a worksheet function and a VBA function in excel.

Syntax

=CHOOSE(index_num, value1, [value2], …)

Arguments

• Index_num. REQUIRED. The position number in the list of values to return. A number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254. Limit of 29 in EXCEL 2003.
• Value, Value2… Value1 is required, subsequent values are optional. A value can be a number, a cell reference, a defined name, a formula/function, or a text value. 1 to 254 value arguments from which Choose selects the a value or an action to perform based on index_num.

Purpose

To get a value from a list based on position

Return Value

The value at the given position

Return Type

string, numeric, date, etc.

Remarks

• If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
• If index_num is a fraction, it is truncated to the lowest integer before being used.
• If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
• If index_num is an array, every value is evaluated when CHOOSE is evaluated.
• The value arguments to CHOOSE can be range references as well as single values.

So, to get the value from the list using position, we use CHOOSE function. However, to get the position of a value in a row/ column/ table, use MATCH Function. Choose can handle up to 254 values but INDEX, or INDEX with MATCH are better approaches for large value sets.

Sources

• https://support.office.com/en-us/article/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc
• https://exceljet.net/excel-functions/excel-choose-function