How to Use the MATCH Function in Excel
Let’s explore the MATCH function, a powerful tool in both Microsoft Excel and Google Sheets that allows you to locate the position of a specific item within a range of cells.
Overview
The MATCH function searches for a specified item within a range of cells and returns the item’s relative position. This function is particularly useful for identifying the location of certain values within a row, column, or one-dimensional array.
Syntax
The syntax for the MATCH function is as follows:
MATCH(lookup_value, lookup_array, [match_type])lookup_value: This is the value you want to find within thelookup_array.lookup_array: This refers to the range of cells in which the search will be conducted.match_type(optional): Defines the type of match. This can be 0 for an exact match, 1 for the closest value that is less than or equal to the lookup_value, or -1 for the closest value that is greater than or equal to the lookup_value. If omitted, the default is 1.
Examples
Below, we illustrate how the MATCH function may be applied in various scenarios:
Finding the Position of a Value in a Range
Consider you have a list of student names from cells A2 to A6 and need to determine the position of “Charlie” within this list. Here’s how you can use the MATCH function:
| Student Names |
|---|
| Alice |
| Bob |
| Charlie |
| Diana |
| Emily |
=MATCH("Charlie", A2:A6, 0)This formula will return 3, indicating that Charlie is the third item in the specified range (A2:A6).
Finding the Closest Match
If you’re dealing with a sorted list of numbers and wish to find the closest value below a certain number, the MATCH function can be tailored to meet this need by setting the match_type to -1. For instance:
| Numbers |
|---|
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
=MATCH(25, B2:B6, -1)This formula will return 2, as it matches the position of the value 20, which is the closest number less than 25.
Error Handling
If the MATCH function does not find a match, it will return a #N/A error. You can manage this using the IFERROR function.
=IFERROR(MATCH("David", A2:A6, 0), "Not Found")This formula outputs “Not Found” if “David” does not appear within the range A2:A6.
By mastering the MATCH function in Excel and Google Sheets, you enhance your capability to efficiently locate and manipulate data positions within your spreadsheet, facilitating streamlined data analysis and management.
More information: https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a