Example of a MATCH function
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. A MATCH function consists of 3 components:
- The lookup value (the name of the employee: Rhys);
- The lookup range, the cell range in which we want to search for the lookup value;
- 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 (lineair) cell range in which we expect to find it: =MATCH(“Rhys”; B2:B23
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.
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 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.