Vlookup options

[rfinnegan]

Hi All:
How can I get VLOOKUP to account for all items that match it, not just the first one? For example, if a PLU (Product Look Up code) is sold at 2 stores, I'd want to see the total of the 2 stores, not just the first one VLOOKUP found.

I can accomplish this using a SUMPRODUCT formula I have, but the data set (6 tabs with about 60K lines and 6 columns per tab) and report are so large (about 700 data points using vlookup), that what was taking VLOOKUP about 6 seconds to update is taking the SUMPRODUCT formula about 1 minute to update.

Here's a sample of the SUMPRODUCT formula. Week 1 is one of 6 tabs. E2 is the store name, H11 is the PLU.

=SUMPRODUCT(--(week2!$C:$C=$E$2),--(week2!$D:$D=$H11),week2!$F:$F)

Any help is appreciated.

 

[Dryver14]

does changing C:C to C1:C100000) and D:D to D1:D100000) speed things up any

 

[Eric W]

Try SUMIFS:

=SUMIFS(week2!F:F,week2!C:C,E2,week2!D:D,H11)

Incidentally, your SUMPRODUCT formula is probably slow because you're using entire column references. If you change $C:$C to $C$1:$C$1000 (or whatever your maximum row is), it will calculate much faster. Without an upper row reference, SUMPRODUCT is forced to check all 1,000,000+ rows.

SUMIFS is "aware" of what the last used row is, so using whole column references in SUMIFS is much more efficient. You can still use row references if you want though.

 

[Aladin Akyurek]

Try SUMIFS:

=SUMIFS(week2!F:F,week2!C:C,E2,week2!D:D,H11)

Incidentally, your SUMPRODUCT formula is probably slow because you're using entire column references. If you change $C:$C to $C$1:$C$1000 (or whatever your maximum row is), it will calculate much faster. Without an upper row reference, SUMPRODUCT is forced to check all 1,000,000+ rows.

SUMIFS is "aware" of what the last used row is, so using whole column references in SUMIFS is much more efficient. You can still use row references if you want though.

SUMIFS is a range-processing function while SUMPRODUCT does array-processing. The latter has an overhead that obtains by converting a reference (range) to an array, an additional cost compared with SUMIFS.

 

[Eric W]

Yes, I'm sure that's true, and a major difference in the calculation time. But according to Microsoft:

https://msdn.microsoft.com/en-us/vb...-tips-for-optimizing-performance-obstructions

For functions like SUM, SUMIF, and SUMIFS that handle ranges, the calculation time is proportional to the number of used cells you are summing or counting. Unused cells are not examined, so whole column references are relatively efficient, but it is better to ensure that you do not include more used cells than you need. Use tables, or calculate subset ranges or dynamic ranges.

Based on that, my statement about SUMIFS knowing the last row is also true. So there are multiple reasons why SUMIFS would work faster.

 

[Aladin Akyurek]

Yes, I'm sure that's true, and a major difference in the calculation time. But according to Microsoft:

https://msdn.microsoft.com/en-us/vb...-tips-for-optimizing-performance-obstructions



Based on that, my statement about SUMIFS knowing the last row is also true. So there are multiple reasons why SUMIFS would work faster.

I am aware of that claim (I re-invoked that myself many times). The thing is that it is hard to identify from the observed behavior the factors which determine that behavior.

Let's assume:

[1] A routine for recognizing the used range.

Does this routine fire up when a function (say SUMIF(S) is fed a whole column/row or with any reference?

Thus:
Fed with A:A, used A2:A100 >> A2:A100?
Fed with B1:B100, used B1:B10 >> B1:B10 or B1:B100?

[2] A routine for transforming a range into an array, e.g., A2:A10 >> {elements of A2:A10}

The thinking is that the code for SUMIFS includes [1] but not [2] because it does not do array-processing, while the code for SUMPRODUCT does not include [1] while it must include [2].

[3] The cost associated with [2] > the cost associated with [1].

Looks like transform(used(range)) is avoided, that is, SUMPRODUCT does always transform(range).

 

[rfinnegan]

does changing C:C to C1:C100000) and D:D to D1:D100000) speed things up any

I tried something similar to that earlier and didn't notice a difference. I chose C:C and D:D because we are transitioning to a new system system which will increase the number of PLUs for the next couple of months. Problem is, I don't know how many PLUs there will be.

Thanks

 

[rfinnegan]

Try SUMIFS:

=SUMIFS(week2!F:F,week2!C:C,E2,week2!D:D,H11)

Incidentally, your SUMPRODUCT formula is probably slow because you're using entire column references. If you change $C:$C to $C$1:$C$1000 (or whatever your maximum row is), it will calculate much faster. Without an upper row reference, SUMPRODUCT is forced to check all 1,000,000+ rows.

SUMIFS is "aware" of what the last used row is, so using whole column references in SUMIFS is much more efficient. You can still use row references if you want though.

Worked perfectly, thanks a million. I knew the entire column reference was going to take time, but we are transitioning to a new system and I don't know what the upper limit is. What I do know is that it is about 60K now and will increase before old PLUs are purged and it gets down to about 35-45K.

 

[Aladin Akyurek]

Worked perfectly, thanks a million. I knew the entire column reference was going to take time, but we are transitioning to a new system and I don't know what the upper limit is. What I do know is that it is about 60K now and will increase before old PLUs are purged and it gets down to about 35-45K.

You can switch to dynamic named ranges.