Excel, MATCH() function

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.


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:

Example 1 Excel MATCH function

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)

Example 1 Excel MATCH function, 1
Result:

Example 1 Excel MATCH function, 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)

Example 2 Excel MATCH function
Result:

Example 2 Excel MATCH function, 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:

Example 3, Excel, 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)

Example  3 Excel MATCH Function horizontal range
Result:

Example  3 Excel MATCH Function horizontal range, 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:

Example 4 Excel, MATCH
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)

Example 4 Excel, MATCH, Formula
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 4 Excel, MATCH, Formula, Result


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:

Example 5, Excel, MATCH

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:

Example 5, Excel, MATCH, Formula
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:

Example 5, Excel, MATCH, Formula, Result

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

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

 

Leave a Reply

Your email address will not be published. Required fields are marked *