Array formula not calculating in linked workbooks

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
110
I currently have a workbook with over 50,000 rows of data based on color, make, model, package, year and price of cars and I'm using an array formula to retrieve price based on multiple criteria. The formula works perfectly when all information is in one workbook, but the array formula does not work when I separate the data in one workbook and the formula in a different workbook. Is there an alternative method to write the formula so I can leave the data set in a separate workbook?

Here is the data set format:
Column A - Color
Column B - Make
Column C - Model
Column D - Package
Column E - Year
Column F - Price

Example output (user selects the combinations):
Cell J2 - Red
Cell K2 - Ford
Cell L2 - Mustang
Cell M2 - GT
Cell N2 - 1967
Formula below = $25,000

Here is the formula:
Code:
{=INDEX($F$:$F$,MATCH(1,(J2=$A$:$A$)*(K2=$B$:$B$)*(L2=$C$:$C$)*(M2=$D$:$D$)*(N2=$E$:$E$),0))}
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,656
Office Version
  1. 2010
Platform
  1. Windows
you could try something like this (also an array formula):

Code:
{=MATCH(H3&I3&J3,CONCATENATE(D3:D7,E3:E7,F3:F7),0)
}
I don't know whether it will solve our problem but it is worth trying since it is a slightly different way of doing it.
Note I haven't put the correct cell references in, I just tried it quickly.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Control+shift+enter, not just enter:

=INDEX([Book1]Sheet1!$F$2:$F$10,MATCH(1,(J2=[Book1]Sheet1!$A$2:$A$10)*(K2=[Book1]Sheet1!$B2:$B$10)*(L2=[Book1]Sheet1!$C$2:$C$10)*(M2=[Book1]Sheet1!$D$2:$D$10)*(N2=[Book1]Sheet1!$E$2:$E$10),0))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,667
Messages
5,597,458
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top