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.
In which row does the string “Americano” appear in the data set below?
While you can see at a glance that the answer is 4, let’s check it out in 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.
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.
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.