When it comes to data analysis in Excel, the ability to find specific data within a range is important. While Excel offers various features and functions for finding and extracting information from a dataset, the MATCH function provides a flexible and efficient solution.

In this article, we will explore the syntax, applications, and examples of using the MATCH function in Excel.

Finding an exact match in a data range using MATCH

What Is the MATCH Function in Excel?

The MATCH function in Excel lets you search for a specific value within a range and returns the relative position within that range. you may then use this position to retrieve the corresponding data from the dataset.

The syntax for the MATCH function is as follows:

How to Use the MATCH Function in Excel

Let’s consider some practical examples to understand how to use Excel’s MATCH function:

Finding an Exact Match

Suppose you have a list of students' scores in a worksheet, and you want to find the position of Alex’s score (88). you’re able to use the MATCH function with match_type0(exact match) to achieve this. Here’s the formula you would use:

This formula will search for the value88in the rangeB1:B7and return its relative position. The result would be5, which indicates that Alex’s score is the fifth value in the list of exam scores.

Finding an approximate match in a sorted range using MATCH

Finding the Closest Match in Sorted Data

Let’s say you have a list of temperatures and their corresponding altitudes sorted in ascending order of altitudes. You want to find the closest altitude match to 1800 in the list. Here’s the formula you will use:

The function will search for the value 1800 in the rangeB1:B7(the altitude column) and return the position of the closest value that is less than or equal to 1800. In this case, the closest altitude less than or equal to 1800 is 1500 (in cellB3), so the formula will return 3.

Handling Errors in the MATCH function using IFERROR

Handling Errors With MATCH

When using the MATCH function, you might encounter certain errors. For example, if the lookup_value is not found in the lookup_array, the MATCH function will return the#N/Aerror.

To handle this, you may use the IFERROR orISERROR functionto provide a more user-friendly message. Here’s an example:

Extracting corresponding product using MATCH and VLOOKUP in Excel

SinceChicagoisn’t found in the range, Excel will displayCity not foundinstead of the error value.

Using MATCH With Other Excel Functions

The MATCH function becomes even more powerful when combined with other functions, such as VLOOKUP, INDEX, and theOFFSET function, to perform advanced data lookup and retrieval tasks.

Using VLOOKUP With MATCH Function

Suppose we have a table with columns:ID,Product,Category, andPrice. We will use VLOOKUP and MATCH to find the corresponding Product and Price for the ID in cellA4. To get the product, use the following formula:

To get the Price:

In this example, the VLOOKUP function will use the value in cellA4(which is the ID) to search for the corresponding row in the rangeA2:D7.

Once it finds the row, it will use the column number returned by MATCH to retrieve theProductandPricevalues.

Retrieving corresponding price value using VLOOKUP with MATCH in sample sheet

Mastering Data Lookups With Excel’s MATCH Function

The MATCH function is an important tool in Excel for locating specific data within a range, and its versatility makes it valuable for various advanced data lookup tasks. By understanding its syntax and various match types, you can efficiently navigate through your data, finding exact or approximate matches as needed.

By combining MATCH with other functions like VLOOKUP and IFERROR, you can create advanced lookup formulas to handle various scenarios and work with large datasets effectively.