# Thread: Lookup with Many Criteria Thanks:  1 Post #5271334 (1) Likes:  1 Post #5271334 (1)

1. ## Lookup with Many Criteria

HI

I have been given a data extract, and due to the criteria I have, the formula based on all of those finally works.
However, it is very heavy and slow - Calculating (4 Threads): XX%

The formula is as follows:

=SUMPRODUCT(--(INDEX(Extract!\$2:\$104857,,1)=E\$2),--(INDEX(Extract!\$2:\$104857,,2)>=\$D3),--(INDEX(Extract!\$2:\$104857,,2)<\$D4),--(INDEX(Extract!\$2:\$104857,,4)=\$F\$1),--(INDEX(Extract!\$2:\$104857,,6)=\$H\$1),--(INDEX(Extract!\$2:\$104857,,7)=\$J\$1),--(INDEX(Extract!\$2:\$104857,,8)))

E2 is the date
D3/d4 is the time
F1 is the service area
H1 is the contact type (phone//email)
J1 is the inbound/outbound
column 8 is the value I am interested in.

It works perfectly for what I need, but the freeze, slow down drags for 10-15 minutes.
Is there anything that can be done to still look through all the 6/7 options and speed up the refresh?

Thanks.

2. ## Re: Lookup with Many Criteria

Hey, try reference the column instead of the entire row, try:

=SUMPRODUCT(--(INDEX(Extract!A\$2:A\$104857,,1)=E\$2),--(INDEX(Extract!B\$2:B\$104857,,1)>=\$D3),--(INDEX(Extract!B\$2:B\$104857,,1)<\$D4),--(INDEX(Extract!D\$2:D\$104857,,1)=\$F\$1),--(INDEX(Extract!F\$2:F\$104857,,1)=\$H\$1),--(INDEX(Extract!G\$2:G\$104857,,1)=\$J\$1),--(INDEX(Extract!H\$2:H\$104857,,1)))

3. ## Re: Lookup with Many Criteria

THanks Tyija.

I thought this had worked as faster but still hanging. Is there anything more than I can do?

4. ## Re: Lookup with Many Criteria

Sorry, that is the only changes I can think of that might have increased the speed of execution... It is a lot of data you're referencing so I can see why it runs pretty slowly.