How To Use MATCH function in Excel Workbook

What is MATCH function in Excel?

The MATCH function in Excel is a lookup function used to find the relative position of a value within a range of cells. It returns the relative position of the value in the range, i.e. the row number or the column number of the value.

MATCH Function Description

The MATCH function in Excel is a function used to locate the position of a lookup value in a row, column, or table. It can also be used to find the closest match to a specified value in a range of cells.

The function works by searching for a specified value in an array or range of cells and then returning the relative position of that value in the range.

For example, if you were looking for the value “apple” in a range of cells, the MATCH formula would return the relative position of the cell containing the value “apple”. This function is especially useful when looking up data in a large dataset.

Struggling with your Excel formulas?
Looking for a faster and easier way to write Excel formulas? Try AI Excel Formula Generator and turn your text into formulas with just a few clicks.

Try for free

MATCH Function Syntax

=MATCH(lookup_value, lookup_array, [match_type])

where lookup_value: the value you are looking for

lookup_array: the range of cells where the lookup value is located

match_type: [optional] 0: exact match, 1: less than or equal to, -1: greater than or equal to

MATCH Function Example in Excel

Match Function MS Excel - Excel formulas and functions - ajelix

In our example we’re trying to find if Employee ID no. 436791 is existing in the table. Match type is 0 because we’re looking for an exact match.

MATCH Formula Example in Workbook

Match function MS Excel - result of the formula

In the picture above you can see that the result is 31 which means that the Employee ID can be found in the 31 row.

How to use MATCH function in MS Excel in your workbook:

Open the Excel workbook that contains the data you want to match.
Select the cell or range of cells in which you want to display the result of the match.
Click the “Formulas” tab and select “Lookup & Reference” from the ribbon.
Click the “MATCH” function from the list of Lookup & Reference functions.
In the Match dialog box, enter the value that you want to match in the “Lookup Value” field.
Click the cell or range of cells that contain the values you want to match against in the “Lookup Array” field.
Select either “0” or “-1” in the “Match Type” field.
Click “OK”.
The result of the match will be displayed in the selected cell or range of cells.

Back to Excel Formula Cheat Sheet

Frequently Asked Questions

What happens if the lookup value is not found in the range?

If the lookup value is not found in the range, the MATCH formula returns a #N/A error.

Can the MATCH formula search horizontally as well as vertically?

Yes, the MATCH formula can search both horizontally and vertically, depending on how the lookup array is specified. If you want to search horizontally, you can use a row or a row range as the lookup array.

What are some use cases for the MATCH formula?

The MATCH formula can be used in various contexts, such as finding the position of a student’s score within a range to determine their rank, matching sales figures to specific products or time periods, or looking up related data from another column based on a common value.