How to Use MATCH in Excel

If you work with Excel frequently, you might have come across MATCH formula. This formula is solely designed to help you find a match for a specific value in a range of cells. The MATCH function is an Excel lookup and reference function, and it searches for a specified value in a range of cells. Unlike many other Excel formulas, MATCH has a straightforward syntax, making it simple for users to understand and use it. In this blog post, you will learn how to use MATCH in Excel and its different variations.

Introduction

If you work with Excel frequently, you might have come across MATCH formula. This formula is solely designed to help you find a match for a specific value in a range of cells. The MATCH function is an Excel lookup and reference function, and it searches for a specified value in a range of cells. Unlike many other Excel formulas, MATCH has a straightforward syntax, making it simple for users to understand and use it.

Syntax and Formula

The syntax for MATCH is as follows:

=MATCH (lookup_value, lookup_array, [match_type])

The lookup_value is the value you want to find a match for. The lookup_array is the range of cells you want to search for the lookup_value. The match_type is optional, and it specifies how Excel should perform the match. The match_type argument can take three different values: 1, 0, or -1.

Match Types

The match_type argument can take one of these three values:

• Exact Match (0): This is the default match type. It tells Excel to look for the exact match of the lookup_value in the lookup_array.
• Less Than (1): This option will return the largest value that is less than or equal to the lookup_value.
• Greater Than (-1): This option will return the smallest value that is greater than or equal to the lookup_value.

Using MATCH to Find an Exact Match

In its simplest form, MATCH can be used to find an exact match for a value in a range of cells. To use MATCH for an exact match, follow these steps:

1. Start by typing “=MATCH(” in the cell where you want to result to be displayed.
2. Select the cell that contains the lookup_value.
3. Type a comma “,”.
4. Select the range of cells that you want to search for a match.
5. Type a closing parenthesis “)” and press Enter.

For example, suppose you have a range of cells from A1 to A5, and you want to find the position of the value “Apple.” Here is what you would type:

=MATCH("Apple", A1:A5, 0)

Excel will search the range A1:A5 for the exact match of “Apple,” and return the position of the matching cell.

Using MATCH to Find the Closest Match

You can also use MATCH to find the closest match for a value in a range of cells. To use MATCH for this purpose, follow these steps:

1. Type “=MATCH(” in the cell where you want to result to be displayed.
2. Select the cell that contains the lookup_value.
3. Type a comma “,”.
4. Select the range of cells that you want to search for a match.
5. Type 1 to find the largest value that is less than or equal to the lookup value, or -1 to find the smallest value that is greater than or equal to the lookup value.
6. Type a closing parenthesis “)” and press Enter.

For example, suppose you have a range of cells from B1 to B5 that contains a random set of numbers, and you want to find the position of the value closest to 4.5. Here is what you would type:

=MATCH(4.5, B1:B5, 1)

Excel will search the range B1:B5 for the closest match of 4.5 and return the position of the matching cell, which in this case is 3.

Wrap Up

The MATCH formula is a useful tool for searching a range of cells in Excel. Whether you need to find an exact match or a value closest to a specific value, MATCH can do it quickly and easily. Hopefully, this guide has given you a better understanding of how to use MATCH in your spreadsheets and has provided you with a few helpful examples to get you started.

Handling Errors with MATCH

Like many other Excel formulas, MATCH returns an error when it cannot find a match. The error value that MATCH returns is #N/A. Here’s an example:

=MATCH("Durian", A1:A5, 0)

In this example, if “Durian” is not found in the specified range, MATCH will return #N/A. You can use the IFERROR function to handle such errors. It’s a simple and effective way to replace any error values with a meaningful alternative. Here is an example:

=IFERROR(MATCH("Durian", A1:A5, 0), "Not Found")

In this example, the IFERROR function will replace #N/A with the text “Not Found.”

Using MATCH with Other Formulas

The MATCH formula can be used in conjunction with other formulas to accomplish more complex tasks. For example, you can use MATCH to find the position of a value in a range and then use INDEX to return the value of the cell in that position. Here is an example:

=INDEX(B1:B5, MATCH("Apple", A1:A5, 0))

In this example, INDEX is used to return the value of the cell in the position specified by MATCH. In other words, this formula would return the value of the cell to the right of the cell containing “Apple.”

Conclusion

The MATCH formula is a versatile tool that can be used to search for both exact and approximate matches in a range of cells. By understanding how to use the syntax of MATCH and its match_type argument, you can easily customize your searches to find precisely what you are looking for. With some practice, you can master using MATCH and become an expert in manipulating data in Excel.

FAQ

Here are answers to some of the most commonly asked questions about using MATCH in Excel:

What happens if I use a range that doesn’t include the lookup value?

If the specified range does not contain the lookup value, then MATCH will return the #N/A error value. You can use the IFERROR function to handle this error and provide a more meaningful response instead of displaying the error value.

Can I use MATCH to search for a partial match?

Yes, you can use MATCH to search for a partial match by using wildcard characters. For example, if you want to find the position of “Apple,” “Apples,” “Red Apple,” or “Green Apples,” you can use the following formula:

=MATCH("*apple*", A1:A5, 0)

This formula uses an asterisk (*) as a wildcard character to match any string of characters before or after the word “apple.”

Can I use MATCH to search for a value in a column and return a value in a different column?

Yes, you can use the combination of MATCH and INDEX to search for a value in one column and return a value in a different column. Here’s an example:

=INDEX(C1:C5, MATCH("Apple", A1:A5, 0))

This formula will search for the position of “Apple” in column A and return the value in the same position in column C.

How do I search for a value in a row instead of a column?

By default, MATCH searches for values in a column. If you want to search in a row, you simply need to transpose the range before using MATCH. Here’s an example:

=MATCH("Apple", TRANSPOSE(A1:E1), 0)

In this formula, TRANSPOSE converts the row A1:E1 into a column, and the MATCH formula then searches that column for “Apple.”

Can I use MATCH with multiple criteria?

Yes, you can use MATCH with multiple criteria by using the combination of MATCH, INDEX, and functions such as IF, AND, and OR. The process involves nesting multiple functions together within the INDEX function to return the desired value based on multiple criteria.

For example:

=INDEX(B1:B10,MATCH(TRUE,IF(A1:A10="Apple",IF(C1:C10="Small",TRUE,FALSE),FALSE),0))

This formula will search for “Apple” in column A and “Small” in column C. If both conditions are met, it will return the value in the corresponding cell in column B.