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: =ISNUMBER(MATCH($A1,$C$1:$C$24,0))

Excel Match Isnumber
Practical use of Excel MATCH formula with ISNUMBER

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.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]