Helper column instead of array?

Example attached

I have 2 workbooks and I need one column of data from workbook 1 into workbook 2.

There are 2 criteria I can use (date and flight number) to identify the needed data (customer name).

I have roughly 108.000 rows of data.

I tried an array formula but after roughly 15 hours it was not done and I assume it never will be.

So, any alternatives? I was considering creating a new column in each workbook by combining date&flight number and then use a standard index-match.

Would this work? How would it work?

Any other tips?

In that example book, you could use A as your helper column
A6: =B6&"|"&C6

Then in I6
=INDEX(D$1:D$110000,match(G6&"|"&H6,A$1:A$110000,0))

In workbook 1 you would join the date and the flight number together, for example put this formula in E6:

=B6&"-"&C6

I've just used a hyphen to separate the two parts. This formula would be copied down to cover all your data. Then in workbook 2 you would have your INDEX/MATCH formula - e.g. use this formula in I6:

=INDEX(D:D,MATCH(G6&"-"&H6,E:E,0))

although you will probably use semicolons ( ; ) instead of commas ( , ) in the formula. Again, this is copied down as far as you need to. You can avoid #N/A errors (where the combination is not found in sheet1), by doing this:

=IFERROR(INDEX(D:D,MATCH(G6&"-"&H6,E:E,0)),"")

Hope this helps.

Pete

My answer was the latest and not necessarily the best but as you can see they are all essentially the same. I will just mention that all three of us included a separator character between the two values (| - +). When you concatenate like this, a date will be converted to an integer. The separator prevents ambiguity between cases like

42824 123

and

4282 4123

There is unlikely to be ambiguity with dates (the difference between the two dates I showed above is about 110 years) but it's a best practice.

I am skeptical that you have the correct array formula, if it is hanging for 15 hours.

I thought about that too, but being an advocate for the use of helper columns, I'm certainly not going to tell someone to NOT use one.. lol.