INDEX/MATCH formula taking too long

**Rob Ardill** · 02-09-2014, 10:27 PM

I have a spreadsheet with a list of products that we re currently promoting. I have created a CSE formula using INDEX and MATCH so that the formula searches for and finds both the product name in the overall list, as well as the specific property of that product (which are identical for each product). This means that there is a lot of processing and calculations going on which is slowing the overall functionality.

I have attached a cut down copy of the spreadsheet or reference. This form is replicated on the same sheet for each individual product we have, currently around 35-40 or so. The formula I have, which is accurate, is as follows:

Formula:

Please Login or Register  to view this content.

"Calculated_Data" is a named range that comprises the total of the product list as attached

To explain in a little more detail, this formula searches for the row number that have the values in B10 and D6 and returns the values from the appropriate columns in those rows, multiplied by a price factor in Q8. What I am looking for is a UDF that can replace this formula as the number of processes and calculations this does repeated many times over is crippling the spreadsheet.

Thanks for your help.

Rob

**martindwilson** · 02-09-2014, 11:01 PM

you could try this non array version of the same formula instead
=IFERROR(INDEX(Calculated_Data,MATCH($B10&$D$6,INDEX(INDEX(Calculated_Data,0,2)&INDEX(Calculated_Data,0,4),0),0),COLUMNS($A$6:Q$6))*Q8,"")