Like the word implies, the MATCH function compares one variable against a list of variables to see if they are equal. When it finds a match, the function returns the row number of the matching variable.
I use it most at work while doing bank reconciliations.
In the following example, I am checking to see if an outstanding cheque is in the list of cashed cheques. That is to see if each value in column B is in column A. The values to enter in the formula are =MATCH(B2,A:A,0). This means, check the value in cell B2 against the list in column A; the zero in the end means exact match.
Excel Match Function
The result in this case is 6 because the number 6487 is the 6th row in column A. Copy the formula till the end of the list in column B to see the status of all values.
There are quite a few #N/A’s which mean error in excel however, in this case it means it cannot find the specified number in the list. Therefore in our context, these cheques which are outstanding have not yet been cashed. You can later sort or filter the MATCH column to group together values.
Match Excel Function
This nifty function could be used in a variety of ways to save you time and reduce error. Your other option is to visually spot the occurrence of numbers on a list which will sure to drive you nuts in an hour or so.
The drawback of the MATCH function is that it stops the moment it identifies a matching value and does to go down the list to see if there are other similar values. In a list of duplicate values, this might not be the best tool to use.
Related posts: