Results 1 to 4 of 4

Thread: Lookup with Many Criteria

  1. #1
    Board Regular
    Join Date
    Sep 2012
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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)))
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    Board Regular
    Join Date
    Sep 2012
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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.
    √-1 2³ ∑ π
    …And it was delicious!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •