The MATCH Function in Excel: A Comprehensive Guide
The MATCH function is one of the most versatile and useful functions in Excel. With MATCH, you can quickly and easily locate values in a table or array. MATCH supports approximate and exact matching, as well as wildcards for partial matches. In this blog post, we will cover everything you need to know about using MATCH in Excel. We will discuss how to use MATCH to find a value in a row or column, how to use MATCH with the INDEX function, and more! So let’s get started!
What Does The MATCH Function Do?
The MATCH function is used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching and wildcards for partial matches. Often, MATCH is combined with the INDEX function to retrieve a value at a matched position.
MATCH only supports one-dimensional arrays or ranges, either vertical or horizontal. You can even use MATCH twice in a single formula to find a matching row and column at the same time!
Where To Find It?
Option 1:
You can find the MATCH function in the Formulas tab of Excel under Lookup & Reference
Option 2:
You can use Formula Builder to walk you through the formula step by step.
Option 3:
You can manually type the formula into any cell.
The Formula
=MATCH (lookup_value, lookup_array, [match_type])
- lookup_value (required) – The value to match in lookup_array
- lookup_array (required) – A range of cells or an array reference
- match_type (optional) – 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest
Let’s Take a Look at A Real-World Examples
Excel Workbook
Coming Soon
Example
Coming Soon
Your turn: Use the workbook and MATCH to follow the examples in blue and finish the practice formulas highlighted in green.
Tips and Tricks
Better Together
The INDEX Function is most often used in connection with the MATCH Function. Check out our post on INDEX(MATCH( for more details.
Receiving An Error
The most common error occurs when the function can’t find a match. This will return an #N/A error.
Let’s Recap
MATCH is a versatile and useful function in Excel. With MATCH, you can quickly and easily locate values in a table or array. MATCH supports approximate and exact matching, as well as wildcards for partial matches. In this blog post, we covered everything you need to know about using MATCH in Excel.
Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.