A question that always gets asked on my Excel courses whenever I cover VLOOKUP is “I have 2 lists. There are some items that are the same on both lists. But there are some items on list 1 that aren’t on list 2. How can I quickly identify these?”
The answer is to use the MATCH function. I’ve produced a short video tutorial and a short written tutorial…
In the spreadsheet below, I want to quickly identify who has attended the introduction course but not attended the advanced course. For demo purposes I’ve manually highlighted 3 names in red – Daniel Wood (A2), Mason Godfrey and Edward Moss (A12). These people have attended the introduction course but not the advanced course.
I can use the MATCH function in the cells in column E to find the information that I require. The MATCH function searches for a given value (in this case a person’s name) within a range and returns the position of that value within the range.
In other words, in E2 I enter a function that searches for “Daniel Wood” (the given value) within the range C2:C14 (the range).
The syntax of the MATCH function in E2 is =MATCH(A2,$C$2:$C$14,0)
A2 contains the value to search for
C2:C14 is the range to search in (I’ve made it absolute so the function can be copied down column E)
0 means that Excel must find an exact match. There are two other possible values for this third argument (which represents the type of match that the function will perform). If you use 1, which is the default, the MATCH function will find the largest value that is less than or equal to the value specified as the first argument. If you use -1, The MATCH function will find the smallest value that is greater than or equal to the value specified as the first argument. Apart from the fact that in this example it must find an exact matching string, if you use 1 or -1, the values in the range must be in ascending order, which in my spreadsheet they are not.
Once the function is copied down column E, there is either a number in each cell or #NA.
As I said earlier, the number represents the position of the value within the range. So for example, Joel Mills (A3) is the first name in the range C2:C14, Eve Ball (A6) is the 11th name in the range C2:C14
If the MATCH function does not find a match, it returns #N/A
I don’t actually need to know which position a particular name is in the list, however, I can interpret the information that is returned as “If there is a number in the cell, then the name exists in both lists, if there is an #NA, it only exists in the Excel Introduction list.”
This isn’t a perfect solution, but it’s a great example for explaining the MATCH function. It would be better to display something like “Introduction Only” or “Both Courses” rather than a number or an #NA and this can be done by amending the function or I could use conditional formatting to highlight the relevant names (just like I did manually in the first screenshot above) but I’ll leave these solutions for another tutorial.