You are here:

Excel/find matching values across workbooks


From a column of seventeen 10 digit varying numbers (column A), in 32 different workbooks, i would like to find the matching numbers that occur in ALL workbooks.
Is this possible & How do i do this please ?

Many thanks in advance



I see that I have misunderstood the question.  Here is a revised answer:

Let us assume that all the workbooks are closed.  In a master workbook you will enter formulas.

Now let us assume that each of the 32 workbooks has a single sheet named Sheet1 and the numbers are found in the range A1:A17 on Sheet1

further assume the 32 workbooks are found in the folder C:\Myfolders\Myfiles

so in your master workbook say in cell A1 you need to bring back all 17 of the numbers from the first workbook.  Assume that the first workbook is MyWorkbook1.xlsx  you would type in A1


then drag fill that down to A17

this will bring back all 17 numbers from MyWorkbook1.xlsx

So the number you seek has to be in this list of number.  You will now enter formulas in B1:AF17

B1: =MATCH($A1,'C:\MyFolders\MyFiles\[MyWorkbook2.xlsx]Sheet1'!$A$1:$A$17,0)

Now drag fill that down to row B17.  That will check MyWorkbook2.xlsx for matches.

The formula will return #N/A if that location does not hold the value sought.  If it does, it will tell you the number of the cell in the lookup range where the number is found.

Now continue in C1
C1: =MATCH($A1,'C:\MyFolders\MyFiles\[MyWorkbook3.xlsx]Sheet1'!$A$1:$A$17,0)

Now drag fill that down to row C17.  That will check MyWorkbook3.xlsx for matches.

Continue for the remain workbooks (out to column AF).

Now one row should have all numbers in it if there is a number found in every workbook.  If there could be multiple numbers found in all workbooks, then any row that is all numbers will reflect numbers that appear in all workbooks.

Hopefully from that general example, you can craft a solution that fits your specific needs.

Tom Ogilvy  
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.