INDEX / MATCH 400k cells in workbook a against 12million cells in workbook b

notanexpertinexcel

New Member
Joined
Jul 4, 2016
Messages
10
I need a simple (efficient) formula that searches workbook2!A1:O800000 for the values in Workbook1!Column1.

Basically, I have a list of 400,000 numbers in columnA in workbook 1. I need to know if these numbers exist in workbook 2 (which has 12 million numbers in it, broken down into columns of 800,000).

I have tried SEARCH, INDEX, MATCH and a combination of IF(INDEX). However, it works on the first number, but then I get no MATCH, when I know there are matches.

I would prefer to search the entire 12 million records and autofill the 400,000 searches to speed up the process.

I can even break down the search to limit to individual columns (of 800,000 by searching column a, b, c, etc)

IN Column B of workbook1 I have the example formula that i'm struggling with:

=IF(INDEX([testnumbers.xlsx]split1!$A:$A,MATCH(A1,[testnumbers.xlsx]split1!$A:$A))=A1,"Match","no match")

Thank you for any help!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

PilsburyKush

New Member
Joined
Jan 16, 2018
Messages
3
That's a heavy amount of data and will take a bit to process... but if I'm understanding the question a simple COUNTIF() will work, if that returns a zero then you know it doesn't exist in your other set of data.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,489
Messages
5,596,455
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top