How to Use the MATCH Formula in Excel

The MATCH formula looks for an item within a range and returns the relative position of that item in the range. It is often used in combination with the INDEX function and is often nested within an INDEX function. However, for the purposes of this lesson we are only going to look at the match function on its own.

MATCH Formula Explanation

= MATCH (lookup_value, lookup_array, [match_type])

Below is a description of the syntax for the MATCH function in Excel.

• Lookup_value (required): The value that you want to search within the range i.e. the lookup_array.
• Lookup_array (required): The range of cells being searched to find the lookup_value.
• Match_type (optional): This is a number being -1, 0, or 1. The match_type argument specifies how Excel matches the lookup_value with values in the lookup_array range. The default value for this argument is 1 which is a “less than” match type which -1 is a “greater than” match type. The 0 match type is used to find an exact match.

Example Using the MATCH Formula In Excel:

Let’s suppose an inventory clerk of a shop keeps track of all purchases in the company’s ERP system with a serial code attached to the purchase.

The inventory clerk wants to know what sale # a particular item sold was on October 31, 2019, as the external auditors are questioning it and want the inventory clerk to pull the receipt. The receipts are kept in a separate drawer depending on whether it was in the morning shift or afternoon shift as there is two staff that work at the shop and they track their receipts separately. Therefore, figuring out whether the sale occurred in the morning or afternoon is important to be able to pull the receipt.

The Financial Analyst prepares a list of product purchases for October 31, 2019, in the below table as it was exported from the ERP system in order of time of sale.

Using this data, the Financial Analyst wants to use an Excel function to determine what sale of the day the item in question relates to so they can pull the receipt for the auditor.

*Note this table is only a sample of an example with 5 data entries, and the formulas are based on the whole population of raw data which are not reflected in the example

Solution:

Using the MATCH function, the Financial Analyst can take the serial code of the item being questioned by the auditors and look it up in the table to determine what purchase it was and whether it would have been in the morning

shift or afternoon shift. As per the below formula result, the serial code shows that it was the 8th purchase of the day.

Based on shop experience, mornings are far busier with purchasing than the afternoons and therefore it is determined likely to be part of the morning shift’s purchases, given that there were only 20 purchases during the day and this was item #8. This will make it a lot easier for the finance team to find and pull the receipt form the morning receipts drawer for the auditors.

The MATCH formula is used as follows for our example:

• Lookup_value (required): Serial Code 00012429 for the transaction in question.
• Lookup_array (required): The Serial Code column in the table.
• Match_type (optional): 0 because we want an exact match.

Therefore, based on this information the financial analyst can pull the morning shift receipts, search through them and try to find the receipt related to this serial code.

The MATCH formula is a simple type of formula where you are basically just looking up a value within a table and trying to identify where it is within that table. It can come in very handy depending on what type of lookup you are performing.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!