What is MATCH in Excel?

The MATCH function looks for a certain value in a specified lineair cell range and returns the relative position of that value within this range. 

Example of a MATCH Function

Below we have a list of employees, first sorted by gender, then by last name.

What is MATCH in Excel - List of Employees :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

We want to know which position in the list the employee ‘Rhys’ has. We can use the Match function to find this position. A MATCH function consists of 3 components:

  1. The lookup value (the name of the employee: Rhys);
  2. The lookup range, the cell range in which we want to search for the lookup value;
  3. The match type, which can be 1, 0 and -1, but ‘0’ is used most frequently. This match type looks for the exact match.

First we select a cell in which we want to publish the position and we start calling the function: =MATCH(:

What is MATCH in Excel - Call Function :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

Then we enter the name that we are looking for, plus the (lineair) cell range in which we expect to find it: =MATCH(“Rhys”; B2:B23

What is MATCH in Excel - Lookup Value and Lookup Range :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

Last we add the match type to the function. We want the match to be exact, so we choose ‘0’: =MATCH(“Rhys”; B2:B23; 0) and press enter. 

What is MATCH in Excel - Exact Match :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

The function returns the position of employee Rhys in the list: 15. Sort of obvious, since his name is in row number 16. What if we want to see what his position is in the list of male employees? We change the lookup range to (B8:B23):

What is MATCH in Excel - Different Lookup Range :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

When we press enter again, we find that Rhys’ (relative) position in the list of male employees is 9:

What is MATCH in Excel - Relative Position :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

Match Types

Beside the exact match type (‘0’), we can use ‘1’ and ‘-1’. 1 finds the largest value greater than or equal to the original lookup value, but only when the values in the lookup array are placed in ascending order. -1 finds the smallest value greater than or equal to the original lookup value, but only when the values in the lookup array are placed in descending order. When the lookup arrays don’t match these conditions, the result will be an error or simply corrupt.

Try PerfectXL Now

 

About PerfectXL

PerfectXL provides full automatic clarity about all risks, mistakes and other problems in your spreadsheets, plus an extensive improvement plan. You don’t have to be an expert yourself and you don’t need much time to fix your file.

Learn about the features

 

Prefer INDEX and MATCH over VLOOKUP

VLOOKUP is used a lot more often than the INDEX and MATCH combination, while the latter are really much better. INDEX and MATCH are less error prone and a lot more efficient.
INDEX & MATCH!

Subscribe to our Excel Guidelines