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.
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:
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
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!