Change from Array formula

[ExcelRoy]

Hi all,

Is there a way to use a formula but not as an array?

=IFERROR(INDEX(Update!$BI$2:$BI$1000, SMALL(IF(COUNTIF($D$8, Update!$BG$2:$BG$1000)*COUNTIF(E16,Update!$AZ$2:$ZA$1000), ROW(Update!$BG$2:$BG$1000)-MIN(ROW(Update!$BG$2:$BG$1000))+1), ROW(A6)), COLUMN(A6)),"")

this formula works great but I need to use it approx. 1000 times so it slows the whole sheet down

is there a better way?

D8 and E16 are search in BG and AZ and BI is returned

Many thanks

 

[DRSteele]

At first glance, I can say without reservation that function IFFERROR is the main culprit here. Can you find another way to control for the errors the array reports?

 

[ExcelRoy]

Hi DRSteele,

Even without the IFERROR it still very slow indeed

Thanks anyway

 

[Aladin Akyurek]

Is this really Update!$AZ$2:$ZA$1000 or Update!$AZ$2:$AZ$1000?

Care to post a scaled down input sample along with the expected output?