MATCH

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

Using MATCH: and example

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

We want to know which position in the list the employee ‘Rhys’ has. We can use the MATCH function to find this position. This 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(:

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

Last we choose whether we want the match to be exact or approximate. We choose ‘0’ for exact: =MATCH(“Rhys”; B2:B23; 0) and press enter.

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

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

Exact or approximate

Beside the exact 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 with the INDEX function 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:

• Prefer INDEX & MATCH over VLOOKUP
• Dedupe in Excel: find and remove duplicate values