Looking for faster option

[SQUIDD]

Hi All

I am looing for a faster and more efficient way.

k2 copied down
=IFERROR(LOOKUP(I2,RESULTS!G:G,-RESULTS!E:E),"")
L2 copied down
=IFERROR(IF(I2=0,LOOKUP(I1+1,RESULTS!G:G,RESULTS!E:E*H2),""),"")

Many Thanks

Dave

 

[azumi]

This part : RESULTS!G:G,-RESULTS!E:E)

will slow your computer down, because it willl calculated until maximum row in your sheet, just extend the ranges based your actual data, maybe in ranges ie: G2:G100 and E2:E100

Cheers

 

[Aladin Akyurek]

Hi All

I am looing for a faster and more efficient way.

k2 copied down
=IFERROR(LOOKUP(I2,RESULTS!G:G,-RESULTS!E:E),"")
L2 copied down
c
Many Thanks

Dave

[1] LOOKUP should be fast as it is applied to a match range (i.e., RESULTS!G:G) which is sorted in ascending order.

[2] If LOOKUP is justified, IFERROR call should not be necessary. Also, why not apply minusing at the result instead of applying it to whole range?

K2:

=-LOOKUP(I2,RESULTS!G:G,RESULTS!E:E)

[3] Again, why does the formula in L2 multiplies every cell of the reference instead of just the lookup result?

L2:

=IF(I2=0,LOOKUP(I1+1,RESULTS!G:G,RESULTS!E:E)*H2,"")

If RESULTS!G:G is not sorted in ascending order, you need to change your approach.