|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

MATCH

MATCH

  • Last updated on

Description

Use this function to search for a specified item in a range of cells and return the row number of that item in the range. Supported in Chains. 

This function is useful for finding the row number of a particular value within a list or table.

 

Syntax

MATCH(lookup_value, lookup_array, [match_type])

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
lookup_value

Yes

 The value you want to find in the lookup_array. A cell reference, a cell range, a number, a text string, or a formula which results in any of these.
lookup_array

Yes

 The range of cells being searched. A range of cells containing possible lookup values.
match_type

No

 Specifies how Excel matches the lookup_value with values in lookup_array. 1 (matches the largest value that is less than or equal to lookup_value)
0 (matches the first value exactly matching lookup_value
-1 (matches the smallest value that is greater than or equal to lookup_value)
Default is 1 if omitted.

 

Examples

Sample data

  A B  
1 10 Apple  
2 20 Banana  
3 30 Cherry  
4 40 Date  

Sample formulas

Use case Formula Explanation and Result
Find the relative position of an exact value in a list. =MATCH(30,A1:A4,0)

This formula works in the following manner:

  • MATCH(: This is the function itself.
  • 30: The lookup_value.
  • A1:A4: The lookup_array.
  • 0: The match_type, specifying an exact match.
  • The formula finds 30 in the 3rd position of the range A1:A4.

Result: 3.
Find the relative position of a text string in a list. =MATCH("Cherry",B1:B4,0)

This formula works in the following manner:

  • MATCH(: This is the function itself.
  • "Cherry": The lookup_value.
  • B1:B4: The lookup_array.
  • 0: The match_type, specifying an exact match.
  • The formula finds "Cherry" in the 3rd position of the range B1:B4.

For this data set, the formula returns 3.
Find the relative position of the largest value that is less than or equal to a lookup value in a sorted list. =MATCH(25,A1:A4,1)

This formula works in the following manner:

  • MATCH(: This is the function itself.
  • 25: The lookup_value.
  • A1:A4: The lookup_array (assumed sorted ascending for this `match_type`).
  • 1: The match_type, which finds the largest value less than or equal to 25.
  • The function finds 20 (the largest value <= 25), which is in the 2nd position.

Result: 2.
Find the last value alphabetically that is less than or equal to a specified value. =MATCH("D",B1:B4,1)

This formula works in the following manner:

  • MATCH(: This is the function itself.
  • "D": The lookup_value.
  • B1:B4: The lookup_array (assumed sorted ascending).
  • 1: The match_type.
  • The function finds "Cherry" (the largest value <= "D"), which is in the 3rd position.

Result: 3.
Find the relative position of the smallest value that is greater than or equal to a lookup value in a sorted list. =MATCH(15,A1:A4,-1)

This formula works in the following manner:

  • MATCH(: This is the function itself.
  • 15: The lookup_value.
  • A1:A4: The lookup_array.
  • -1: The match_type, which requires the list to be sorted in **descending** order.
  • Because the lookup_array (A1:A4) is sorted ascending (10, 20, 30, 40), the function cannot find a match.

Result: #N/A.
Find the last numeric value in a column. =MATCH(9.9E+307, B:B)

This formula works in the following manner:

  • MATCH(: This is the function itself.
  • 9.9E+307: The lookup_value, a very large number used to find the last numeric value.
  • B:B: The lookup_array (all of column B).
  • The function searches for the largest number less than or equal to the lookup value. Since column B contains only text, no match is found.

For this data set, the formula returns the error #N/A.
Return the value of the last numeric cell in a column. =INDEX(B:B, MATCH(9.9E+307, B:B))

This formula works in the following manner:

  • MATCH(9.9E+307, B:B): This inner function attempts to find the last numeric value in column B, but returns #N/A as column B only contains text.
  • INDEX(B:B, #N/A): INDEX receives the #N/A error from the MATCH function and returns the same error.

For this data set, the formula returns #N/A.

 

Notes

  • MATCH returns the position of the found item as a number.
  • If match_type is 0, MATCH finds the first value exactly equal to lookup_value.
  • If the lookup_array is not sorted as expected, MATCH may return incorrect results without any error indication. This is because the function stops searching as soon as it finds a value that satisfies the condition, assuming that no better match will follow due to the expected sorting. If you're not sure about the sorting of your data, it's safer to use "0" (exact match) as the match_type, or to explicitly sort your data before using MATCH.
    • If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The lookup_array must be in ascending order.
    • If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be in descending order.
  • If MATCH can't find a match, it returns the #N/A error.
  • MATCH is case-insensitive.

 

Tips

  • If you are not getting the results you expect, check that your lookup_array is correctly sorted for the specified match_type.
  • You can use MATCH with INDEX to create a powerful lookup formula that's more flexible than VLOOKUP. The page Using INDEX-MATCH to create flexible lookups provides an example.
  • You can combine MATCH with other functions such as MIN, MAX, or AVERAGE to find the position of specific values in a range.

 

Related functions

DescriptionUse this function to search for a specified item in a range of cells and return the row number of that ite...