In this post we will explain where you can use the MATCH formula
What can I do with it?
This formula can check an array for you (for something that you want to match) then return the position of the found item.
So in real life terms we can use this to compare two list.
Is one item present in the other list?
What does the formula needs?
= MATCH($A1,$C$1:$C$24,0) – Something that we want to match
= MATCH($A1,$C$1:$C$24,0) – An Array where we want to look
= MATCH($A1,$C$1:$C$24,0) – Specify what kind of a match we want
In the gif below you can see a practical way of finding which numbers are present in the list on the right.
Once we found the matches with the formula we will wrap the MATCH in an ISNUMBER formula to get TRUE or
FALSE for the Conditional formatting
The formula below in B1 is:
So there you go, very quickly you were able to compare two lists.
Sure. Above it is not a big deal, but if you have hundreds or thousands of lines then it is a big big timesaver.
One important limitation of the formula!
MATCH will always give you the first item it finds. So if you have a number represented twice in the column which you trying to match up it will always give you the first found item in return.
It is possible to get back the second or third item, but that is a much more advanced formula.
ExcelAngel was built for the everyday worker so you can succeed in your position with the help of an angel. ExcelAngel is not magic it is simply a collection of solutions for the practical issues in an office environment.
Find about the author.
Did you know that we have a Facebook page for ExcelAngel?
Come and give us a thumbs up if we helped you today.