You are here:

Excel/comparing 2 sets of numbers to find same

Advertisement


Question
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

Thank
Marcus

Answer
Marcus,

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

=match($A2,Sheet1!$A:$A,0)
use that in Sheet2  or in Sheet1
=match($A2,Sheet2!$A:$A,0)


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

=Index(Sheet1!$C:$C,match($A2,Sheet1!$A:$A,0),1)


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 twogilvy@msn.com telling me what version of excel and specifically what you want to do and where.  (don't just say, "Here's my workbook".)

--
Regards,
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

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.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.