Excel glossary

How to use 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:

List of employees in an Excel file // PerfectXL Spreadsheet Validation

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(:

Call the MATCH function in Excel // PerfectXL

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

Lookup value and lookup range in a MATCH function // PerfectXL

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.

Exact or approximate match in Excel function // PerfectXL

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):

Changing the lookup range for relative search in Excel // PerfectXL

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

Relative position in a MATCH function // PerfectXL

Exact or approximate

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.

INDEX & MATCH

The combination INDEX & MATCH is popular for many purposes and a solid alternative to the VLOOKUP function. For more information about the combined use of these functions, read the following articles: