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!
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!