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

[notanexpertinexcel]

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!

 

[PilsburyKush]

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.

 

[PaddyD]

On smaller datasets, an if(isnumber(match.. construction is more efficient than countif(), as outined here:

https://www.mrexcel.com/forum/excel-questions/38643-auto-flag-list.html

...no idea what it'll be like on that amount of data other than slow!