Avoid an Array Function by using Sumproduct

Hi All,

I am trying to turn an array function which working well for me into a normal function just to reduce processing time in my workbook.

I have the following array functions with some named ranges:

=IFERROR(INDEX(RangeOfNames,MATCH(MIN(IF((RangeOfS.Dates<=$A3)*--(RangeofExp.Dates > ($A3+B$1)) > 0,(RangeOfS.Dates<=$A3)*(RangeofExp.Dates)*(RangeofExp.Dates>($A3+B$1)))),RangeofExp.Dates,0)),"None")

=IF(B3="None","NA",IFERROR(INDEX(RangeOfNames,MAX(ROW(RangeOfNames)*(RangeOfS.Dates<=$A3)*(RangeofExp.Dates > =$A3)*(RangeofExp.Dates < VLOOKUP(B3,DataTable,3,0)))-1,1),B3))

I have tried embedding the MIN and MAX functions inside a Sumproduct function with the intention of avoiding Array (Ctrl + Shift + Enter), but I dont get the same results (as derived by array functions) when I do so.

Any help is appreciated,

BR,

Imran

1. SP can also be an array formula, depending on how it is used.
2. Helper columns can help cut down processing time, and keep formulas simple.
3. why would you upload a text document, when your question is regarding an excel file?

Blocking you from what?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Originally Posted by ibuhary

When I was adding my formula here, the system was blocking me

That's because Sucuri does not like < and > without spaces either side. For simplicity, I've added spaces to your formulae and posted them in your opening post.

However, you should upload the workbook as Ford has advised.

I think we need to see a sample workbook, along with samples of your expected outcome.

Without seeing examples, judging from your formulas, it looks like your using them to pull text. If thats the case, SUMPRODUCT itself will not work as it will return numbers. You may be able to use SUMPRODUCT to get a row number as the argument for INDEX but its possible that it would not improve efficiency at all. We'd have to see the formula in context to know.

My recommendation, is to use alternate approach, if speed is prohibitive.

1. PowerPivot - Using DAX, add measures/calculated field (avoid calculated columns as much as you can for performance).
2. VBA - Using Collection/Dictionary and variant array, perform lookup operation.

VBA will be much faster, but PowerPivot will allow for additional analysis using same model and is more flexible.