The MATCH function in Excel is used to search for a specified item in a range of cells and then return the relative position of that item within the range. Here’s how you can use the MATCH function effectively:
Syntax
MATCH(lookup_value, lookup_array, [match_type])
- lookup_value: This is the value you want to search for.
- lookup_array: This is the range of cells that contains the data you want to match against.
- match_type: This is optional and specifies how Excel matches the `lookup_value` with values in `lookup_array`. The options are:
- `1` (or omitted): Finds the largest value that is less than or equal to `lookup_value`. The `lookup_array` must be in ascending order.
- `0`: Finds the first value that is exactly equal to `lookup_value`. The `lookup_array` does not need to be sorted.
- `-1`: Finds the smallest value that is greater than or equal to `lookup_value`. The `lookup_array` must be in descending order.
Using the MATCH Function
- Exact Match Example:
If you want to find the position of the value “Banana” in a list:
=MATCH("Banana", A1:A10, 0)
- Approximate Match in Ascending Order:
Use this if your data is sorted in ascending order and you want to find the position of the nearest lower or equal value:
=MATCH(50, B1:B10, 1)
This would return the position of the largest value that is less than or equal to 50.
- Approximate Match in Descending Order:
If your data is sorted in descending order and you need to find the smallest value greater than or equal to a given number:
=MATCH(20, C1:C10, -1)
Important Notes
- If `match_type` is set to `0` and no exact match is found, the function will return an error `#N/A`.
- If `match_type` is `1` or `-1` and no match is found under the specified conditions, Excel will also return `#N/A`.
- For `match_type` `1` or `-1`, ensure your `lookup_array` is correctly sorted to avoid unexpected results.
The `MATCH` function is often used in conjunction with other functions like `INDEX` to create more complex lookups.
Unlock Your Potential
Excel
Access
Power BI
