You are here:

Excel/comparing 2 sets of numbers to find same


Hi Tom

I have 2 columns of products codes, one is the product codes i current use and one is a master list containing all the product codes and updated pricing.

I need a formula that will search the master list and find the numbers that match my list so i don't have to search thousands of lines of information to find the ones that only i need to act upon

Happy to send an example if needed



Say your master list is in column A of sheet1 and your other list is in column A of sheet2

in column B of Sheet2 you could do this

Sheet2, B2:  =countif(Sheet1!$A:$A,A2)

then drag fill down column B

this will indicate which items in the second list are found in master.

Or you could go the other way.

Sheet1, B2:  =countif(Sheet2!$A:$A,$A2)

(this could be any column in sheet1 if column B is already occupied)
then drag fill down the column.

the cells containing a number other than zero are the ones that match.  If the number is greater than 1, then you have duplicate entries.

If you wnat to find what row in the other sheet you could use

use that in Sheet2  or in Sheet1

If I wanted to pull back the value in say Column C of the matching row I could use


match will return a #N/A error if no match is made.  If you have Excel 2007 or later you could do

=IfError(Index(Sheet1!$C:$C,match($A2,Sheet1!$A:$A,0),1),"No Match")

If none of that helps and you want to send a workbook, then send it to telling me what version of excel and specifically what you want to do and where.  (don't just say, "Here's my workbook".)

Tom Oglvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.