Course Content
Excel Formulas
Excel Formulas
MATCH
Introduction to Lookup and Reference Formulas
Lookup and reference formulas help you find specific data points within a table or range, reference values from different sheets, and create dynamic data analysis models. While there are many such formulas, we will focus on the following ones:
They are among the most important formulas in Excel. When dealing with thousands of rows and many columns, it is crucial to be able to find specific values efficiently.
The most efficient one among these is the XLOOKUP formula, but it can be replaced with a combination of simpler formulas. We will get to it at the end of this section, but for now, let's start with the MATCH formula.
Match
MATCH searches for a specified value within a range and returns the relative position of that value. It is primarily used to search for a specific item in a range of cells and then return the relative position of that item within the range.
| lookup_value | The value you want to search for in the lookup_array. |
| lookup_array | The range of cells that contains the value you want to find. |
| [match_type] | (Optional) Argument that specifies how Excel matches the lookup_value with values in the lookup_array. It can be 1, 0, or -1: |
| 1 (Default): Finds the largest value less than or equal to the lookup_value. | |
| 0: Finds the first value that is exactly equal to the lookup_value. | |
| -1: Finds the smallest value greater than or equal to the lookup_value. |
The true power of MATCH comes when it is combined with the INDEX function. This combination allows you to look up a value in a table based on its relative position.
The MATCH function finds the position of value we are looking for in a range and the INDEX function uses this position to return the corresponding value figure from the other range.
Dynamic MATCH with Named Ranges
Using named ranges makes your formulas more readable and easier to manage. Let’s apply the MATCH formula to named ranges.
To use this, you need to define a name for the range. To do this, go to the Formulas tab and look for the Name Manager section.
Let's create a named range for Months and for the sales. Months will be a name for a range A1:A6 and Sales for the B1:B6. With this we can improve our previous formula to something like this:
We can go even further and refer to a cell instead of writing a literal value to look up (like "April"). By creating a drop-down list for the cell we refer to, we can dynamically update and check the sales values for the selected month.
Thanks for your feedback!
