Match Function: How it Works and 10 Ways to Use It

The match function is the one that search for a specific item/value in a cells' range, and then return the position that is relative to the item in the range.

Parameters and Arguments

The function has a lookup value, array and match type, and looks like this: MATCH (LOOKUP_VALUE, LOOKUP_ARRAY, [match_type]).

The descriptions of the arguments:

Lookup Value: This is an essential part of the function. It is the value that the user wishes to match in the lookup array.

Lookup Array: This is required part of the function. It is a cells' range that would be searched.

Match Type: This is optional, and can either be 0 or 1.

Example 1: Simple Match Formula

We are using a match formula to find out if the price has been set for a product.

Simple Match Formula

Example 2: Where is a Product?

The business' products have been layout in a Microsoft Excel. We are now trying to find out where a product is in the list. Thanks to Match function in Excel, it is very easy to find it.

Where is a Product

Example 3: IF and MATCH

The product has been listed. But, we are trying to find out the price of the product. But, we'd know the price of a product, and we'd want to answer if the price is right. This is why we find IF and MATCH formulas useful.


Example 4: Finding Employee with MATCH

The employees are quite many. But, the executive do not know all of them, and would like to find what row the employee has a name, and we could not find out the email. There are other functions that could be used for finding it, but we prefer using the match to find out the row. This was meant to find out what row to go to, in order to get the email of that employee.

Finding Employee with MATCH

Example 5: Double Match Formula

We'd know the name of the product, but we don't know if the price was correct or if they are on the same row. This made us worried about acknowledgement of the inputted information, which is why we find MATCH function in Excel to be very useful for verifying that the data is both completed and in the same row. We are now going to use double MATCH formula to find the answer that we are looking for.

Double Match Formula

Example 6: Did we Send Right Details and Information?

We are sending a product to a customers. The price has been set, but due to a specific deal we made with the client, we would like to make sure that the price was correct. We have determined that the client would only get to pay a specific amount of money, which is why we will use the IF and double match formula.

Did we Send Right Details and Information

Example 7: Is Everything in The Same Row?

It is easy to list all the products that are being sold. But, the company has just fired one of its workers for inadequacy in their performance, and one of them was that they frequently miss one critical details that will end up costing the company money. The employee would not miss something small, but he would put 100 dollars beside a product that cost 1500. This is why we are going to use triple MATCH formulas to ensure that the expensive products have right layout. These would allow me to ensure that everything is in order.

Is Everything in The Same Row

Example 8: Complete Text

The whole data has been layout as text, and we are looking for a product. We are using the same data as previous one.

Complete Text

Example 9: Is Our Product in Another Spreadsheet?

We have now gone from having our data in one sheet, and would like to verify that the same data in one is matching the other.

Is Our Product in Another Spreadsheet

Example 10: Information from another Document

The situation is that we have our data ready, and now we need to find the solid correspondent between both data, so we could determine if our data has been solid. We'd have the previous year in another document, and this year is in the current one, which is why we need to verify that the data is coherent. It is about acknowledging that the total is there too, so we could find it.

Information from another Document