Looking for faster option

SQUIDD

Well-known Member
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

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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

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.

Last edited:

Replies
0
Views
339
Replies
20
Views
679
Replies
1
Views
104
Replies
6
Views
506
Replies
4
Views
598

1,203,180
Messages
6,053,956
Members
444,695
Latest member
asiaciara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back