help with array formula

[amesassoc]

I routinely need to interrogate large databases to find out if certain core numbers can be found in the databases "catalogue numbers".
I have a formula for finding all such occurrences, which is this array formula:
{=or(is number(find(lookuparray,database,1)))}
This formula will give me a "True" or "False", meaning whether that database entry has one of the core numbers imbedded in its catalogue number.
I now need a formula that will tell me which core number was found.
The last time I needed this was this week when the database was comprised of 1.6 million items and there were over 1,400 core numbers, with the number of "True" entries numbering approximately 20,000.
I had to manually run a simple "find" formula on each of the "True" items using the core numbers and that necessitated around 500 separate manual passes.
Anybody able to solve this?
I would obviously be greatly appreciative.
Thank you.
Amesassoc

 

[RonB1111]

See if this approach using MSQuery on Excel tables is of interest:
Use MS Query to Treat Excel As a Relational Data Source, Page 1

If your database is not in excel (excel only has 1,048,576 rows), you can also use MS Query to pull the database from an external source.

The process is actually not complicated and you don't need to know SQL to use it. If you want to pursue this approach and need further assistance, please reply with some more specifics about your database and lookup array.

 

[amesassoc]

See if this approach using MSQuery on Excel tables is of interest:
Use MS Query to Treat Excel As a Relational Data Source, Page 1

If your database is not in excel (excel only has 1,048,576 rows), you can also use MS Query to pull the database from an external source.

The process is actually not complicated and you don't need to know SQL to use it. If you want to pursue this approach and need further assistance, please reply with some more specifics about your database and lookup array.

Is this to accomplish the first step outlined in my post? In the example I gave, I had two worksheets to accommodate the size of the database, but that didn't pose too much of a problem; I ran the formula twice and combined the results. If this is to identify the specific core number, I would like to pursue. I have not used MS Query so it is all foreign to me. I was hoping to address the second step with an array formula, as I am more familiar with that.
Thanks for the post.
Amesassoc

 

[RonB1111]

Here's a brief summary MS Query approach and the results it would deliver.

1. You assign names to your database (actually 2 separate databases, so 2 separate names) and to the range of cells containing the catalogue numbers you want to look up (sounds like you've already assigned names to everything)
2. For Excel 2007 plus, you start with Data - Get External Data - From OTher Sources - From Microsoft Query
3. A Choose Data Source box opens and you select Excel Files, OK, then in the Select Workbook box, select the name of the file your database(s) and catalogue numbers are in (you'll need to do all these steps twice -once for each database)
4. You'll select one database and the catalogue lookup table, then you'll join them by the common catalog number, select which fields you want displayed on your output results, and enter any criteria to bring back only records matching the criteria
5. You'll send the results to Excel and depending on how you joined the tables, you'll see either only matches, or every item on your lookup array which will have data if a match occurred or no data if a match did not occur
6. You can then use Excel AutoFilters (or a pivot table) to easily further analyze the results of the matches & non-matches.

The 1st 3 steps will get you started. Steps 4 & 5 are very summarized and you may be able to get thru the Wizard yourself, or you may need help with some of it. Step 6 is in place of array formulas.

 

[amesassoc]

I appreciate this submission but am wondering if anyone can suggest an approach in line with my original request, using the results of the initial pass using the formula cited in my original post?
Thank you
amesassoc

Here's a brief summary MS Query approach and the results it would deliver.

1. You assign names to your database (actually 2 separate databases, so 2 separate names) and to the range of cells containing the catalogue numbers you want to look up (sounds like you've already assigned names to everything)
2. For Excel 2007 plus, you start with Data - Get External Data - From OTher Sources - From Microsoft Query
3. A Choose Data Source box opens and you select Excel Files, OK, then in the Select Workbook box, select the name of the file your database(s) and catalogue numbers are in (you'll need to do all these steps twice -once for each database)
4. You'll select one database and the catalogue lookup table, then you'll join them by the common catalog number, select which fields you want displayed on your output results, and enter any criteria to bring back only records matching the criteria
5. You'll send the results to Excel and depending on how you joined the tables, you'll see either only matches, or every item on your lookup array which will have data if a match occurred or no data if a match did not occur
6. You can then use Excel AutoFilters (or a pivot table) to easily further analyze the results of the matches & non-matches.

The 1st 3 steps will get you started. Steps 4 & 5 are very summarized and you may be able to get thru the Wizard yourself, or you may need help with some of it. Step 6 is in place of array formulas.