Avoid an Array Function by using Sumproduct

[ibuhary]

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