Array formula not working with Linked Workbooks

Edit I forgot to mention that both workbooks need to be open for this to work.

You will need to include reference to the other workbook in question.

Formula:

Please Login or Register  to view this content.

It will still need to be array entered.

5 columns of 50,000 rows is a lot of array to process.

As an alternative use a helper column in the data source file that references the criteria in the lookup file. The formula in the lookup file then references the helper in the data source file.

Using the attached the helper in 'Automobile Values File.xlsx' column G ... non array ...

Formula:

Please Login or Register  to view this content.

Then the final formula in 'Automobile Lookup Workbook.xlsx' could be

Formula:

Please Login or Register  to view this content.

or use PowerQuery free add-in for Ex2013 from MS site

• unpack and move folder linked to the C:\
• open linked.xlsx and type there what you want to find
• then refresh green table (right click and select refresh)
• source file can be opened or closed - it doesn't matter

but
PowerQuery is required

Yes. You are welcome, and thank you for the added rep.