Share this article:

What it Does – Locate the position (often the row number) of an item in an array.

Arguments: MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value – the value you are looking up (not case sensitive)
  • lookup_array – the range or array you’re searching
  • [match_type] – Exact match type or other type.

Example:

In which row does the string “Americano” appear in the data set below?

match function

While you can see at a glance that the answer is 4, let’s check it out in Excel.

match function excel

While we specified the string “Americano” in this formula, we can also reference a cell containing the string so that it can be changed later.

match function

In this case the first argument is referencing F2, which contains the item we want to lookup. Let’s search instead for “Latte” by changing F2.

match function

Notice that we did not change the formula (only the item we’re looking for) and the match function returned row number 5 as the result.

Note: While this may seem like a very trivial function to understand, once you learn the MATCH() function, you can combine it with the INDEX function to create a very powerful alternative to VLOOKUP.

 

Please remember that we may receive commissions when you click our links and make purchases