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.

Everything was clear?