Excel, MATCH() function

Jun 30, 2015 by azurous in Download

In this article I will explain the MATCH() function in Excel. This function searches a 1 dimensional range for a target value. If the value is found the index of the value in the range is returned.

You can download the file related to this article from the link below.

Excel MATCH function.xlsx

–

Contents

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value: The value to search for.

lookup_array: The 1 dimensional range to search for the lookup_value

match_type: This is an optional parameter that accepts 3 values:

0: Finds an exact match
1: Finds the largest value that is smaller than or equal to the lookup_value. Only works when the data is in ascending order.
-1: Finds the smallest value that is larger than or equal to the lookup_value. Only works when the data is in descending order.

–

Example 1:

Assume we have the following data in a sheet:

Lets say we want to figure out the row with the name “David”. This could be done using the formula below:

=MATCH("David", B2:B7, 0)

Result:

The result is the number “2”, which is the index of the value “David” in the range “B2:B7”.

–

Example 2:

In this example we will be using the same data as the previous example. In this example the user will input the target name in cell G1. Cell G2 will display the row index of the name. This formula was used in cell G2:

=MATCH(G1, B2:B7, 0)

Result:

The result is the number “3 which is the index of the name “John” in the range “B2:B7”.

–

Example 3:

The MATCH() function could also be used on horizontal data. Assume we have the following data in row 1:

The user writes the lookup_value in cell B3. The column index of the lookup_value is returned in cell B4:

=MATCH(B3, A1:H1, 0)

Result:

–

Example 4, Approximate Match:

In the previous examples the MATCH() function found an exact match. If the values in the range are in ascending order we can tell the MATCH() function to find an approximate match. By inputting the value “1” as the last parameter to the MATCH() function it will return the index of the largest value that is less than or equal to the lookup_value.

Assume we have the following data in column A:

In this example the user inputs a value in cell D1. The index of the largest value that is smaller than or equal to the value in cell D1 will be displayed in cell D2. The formula used in cell D2 is:

=MATCH(D1,A1:A24, 1)

Result:

It can be seen from the figure below that the largest value that is equal to or smaller than the value in D1 (0.42) is the value 0.41662789 in cell A9:

–

Example 5, Approximate Match:

Similar to the previous example, if the values in the range are in descending order, the MATCH() function can be used to return the smallest value that is greater than or equal to the lookup_value. This can be done by using the value “-1” as the last parameter.

Assume we have the following data in column A which is in descending order:

Similar to the previous example the user inputs a value in cell D1. This time the index of the smallest value that is larger than or equal to the value in cell D1 will be displayed in cell D2. The formula used in cell D2 is:

Result:

It can be seen from the figure below that the smallest value that is equal to or larger than the value in D1 (0.39) is the value 0.39522207 in cell A19:

You can download the file related to this article from the link below.

Excel MATCH function.xlsx