Index.match formula too slow to calculate

[mrjayps]

Hi,

I have a formula which works, although it takes too long to update. Am using it to update approx 100 000 rows of data.

I have tried to turn automatic calculation off and manually calculate and replace with values, however still not helping.

Any better way of achieving the same result with a quicker calculation speed.

Thanks
JP

{=INDEX(Sheet1!$A:$C,MATCH(1,(ROW($A1)>=Sheet1!$B:$B)*(ROW($A1)<=Sheet1!$C:$C),0),1)}

Original problem as follows:

Sheet 1:
Column A = Ref Number, Column B = Row Start Number, Column C = Row End Number

ie. ROW 1 Column A ='BTA340', Column B = '1', Column C = '250'
ROW 2 Column A ='ALP340', Column B = '251', Column C = '1250'

I need Sheet 2 to display as follows

ROWS 1 - 250 Column A should read 'BTA340' for all 250 rows
ROWS 251 - 1250 Col Column A should read 'ALP340' for all 1000 rows

Please help!!

 

[Rada1]

Try instead of putting whole column references inside formula, to select actual data (whole column references inside array formulas slow down calculation).

 

[mrjayps]

Thank you so much, SOLVED! That turned a 30 minute calculation to a couple of seconds!!!

 

[Rada1]

You are welcome, glad I helped!