Whenever you need to cross reference two lists, the MATCH function is a pretty useful function to have in your arsenal. Just as the name implies, it checks whether an item in one list is present in another list. If it is, it returns a number that indicates in which row that item is located in.
How it works
Here we have a list of fruits and vegetables and we need to find out if Grapes is on the list.
The MATCH function is a simple one; it will take whatever you’re searching for, try to find it in the list you’re cross referencing it against, and return the row its located in. In our example, we are searching for grapes. The MATCH function tells us it’s located in row number 6.
Lets breakdown MATCH a little further.
The Syntax
Lookup Value – This is the value that you’re trying to cross reference with your list.
Lookup Array – This list you want to cross-reference against.
Match Type – This will tell excel whether to use an exact or relative match (much like the VLOOKUP). You can either enter a 1, 0, or -1. I usually enter 0 (exact match) which means the list doesn’t have to be sorted a certain way and will return the exact row of the value in question.
Lets Get Practical
There are many uses for the MATCH function; I tend to use it mostly as a more precise substitute for the VLOOKUP when I’m looking to cross-reference different databases. However, just like a lot of other excel functions, MATCH is most useful when nested with other functions to create even more automation.
Another use for MATCH would be to see if an item in a running list has been duplicated. Lets say you have a long list of invoices and you need to ensure there aren’t any duplicated values. There are many ways to accomplish this, but one way is to use a MATCH formula with a mixed cell reference (See my previous chapter on absolute vs. relative references for more details).
Here is an example of what I’m talking about:
=MATCH(A15,$A$1:A14,0)
Notice the mixture of relative and absolute reference. This allows you the flexibility of writing your formulas where the reference table expands as you drag your function downward. The formula I created checks any of the prior records and indicates whether the invoice number already exists (and in which row). Based on the last screen capture it looks like three of our invoices have already been duplicated!
You can then build an IF Statement around this MATCH function if you want excel to execute an action whenever it comes across a duplicate number (like the text “DUPLICATE”).
I added the text in green to show you what I mean:
=IF(MATCH(A15,$A$1:A14,0)>0,”DUPLICATE”, MATCH(A15,$A$1:A14,0))
If you are like me and hate seeing errors (that last screen capture makes me cringe) you can also wrap that IF/MATCH formula within an IFERROR and get rid of all those #N/A errors.
=IFERROR(IF(MATCH(A15,$A$1:A14,0)>0,”DUPLICATE”, MATCH(A15,$A$1:A14,0)),””)
The important thing is to practice and get creative. There is no limit to the formulas you can come up with that will help improve and automate your work.