Return to Excel Formulas List

Comparing Items in a List

Consider the following two lists:

comparing lists

And that we want to see which items from column A are in Column B. This can be achieved by the MATCH function in conjunction with ISNUMBER .

The MATCH function has the following syntax:


And this will return the position of the value cell A6 in the range C4:C13 – the “0” is for an exact match. So this will return the number 2 – as “Ship” (the value in cell A6) has position 2 in the range C4:C13. If an exact match can’t be found then an N/A# is returned.

Combining this with a ISNUMBER function we have:


Will return TRUE when there is a MATCH and a FALSE when there is no MATCH.

So we can put the following in cell B4 and copy down:


This will return the expression “MATCH” when the value in Column A can be found in Column B

comparing lists v1

To download the .XLSX file from this tutorial, click here.

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List