Which is faster? SUMPRODUCT or VLOOKUP, or another alternative?
 

=VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)


=SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$300 0)

Which is faster? SUMPRODUCT or VLOOKUP, or another alternative?
 

What do you mean by faster? Processing wise/typing wise?


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=545500

Which is faster? SUMPRODUCT or VLOOKUP, or another alternative?
 

Whilst I would (reasonably) guess that VLOOKUP is faster, it is not just a
question of speed but of functionality.

If there are two items in the lookup range with the value in C44, VLOOKUP
returns the value associated with the first, SUMPRODUCT sums both associated
values. It depends upon what you want to do which one you choose.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"SteveC" wrote in message
...
=VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)


=SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$300 0)

Which is faster? SUMPRODUCT or VLOOKUP, or another alternative?
 

I reckon this is one for Harlan!

ANdy.

"Bob Phillips" wrote in message
...
Whilst I would (reasonably) guess that VLOOKUP is faster, it is not just a
question of speed but of functionality.

If there are two items in the lookup range with the value in C44, VLOOKUP
returns the value associated with the first, SUMPRODUCT sums both
associated
values. It depends upon what you want to do which one you choose.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"SteveC" wrote in message
...
=VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)


=SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$300 0)

Which is faster? SUMPRODUCT or VLOOKUP, or another alternative?
 

Any fool can time them, doesn't change the fact that as always, it depends
....

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

<Andy wrote in message ...
I reckon this is one for Harlan!

ANdy.

"Bob Phillips" wrote in message
...
Whilst I would (reasonably) guess that VLOOKUP is faster, it is not just
a
question of speed but of functionality.

If there are two items in the lookup range with the value in C44,
VLOOKUP
returns the value associated with the first, SUMPRODUCT sums both
associated
values. It depends upon what you want to do which one you choose.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"SteveC" wrote in message
...
=VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)


=SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$300 0)

Which is faster? SUMPRODUCT or VLOOKUP, or another alternative?
 

Whilst I would (reasonably) guess that VLOOKUP is faster

.....and, if the lookup table is sorted ascending, Vlookup is significantly
faster than if the table is not sorted.

Biff

"Bob Phillips" wrote in message
...
Whilst I would (reasonably) guess that VLOOKUP is faster, it is not just a
question of speed but of functionality.

If there are two items in the lookup range with the value in C44, VLOOKUP
returns the value associated with the first, SUMPRODUCT sums both
associated
values. It depends upon what you want to do which one you choose.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"SteveC" wrote in message
...
=VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)


=SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$300 0)

Which is faster? SUMPRODUCT or VLOOKUP, or another alternative?
 

A couple of comments...

1. Don't substitute Summing for Retrieval, unless there are no duplicate
records. If "no duplicate records" qualification holds...

=VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)

should be mapped onto a SumIf formula, not onto a single-condition
SumProduct formula:

=SUMIF(Sheet1!$C$13:$C$300,C44,Sheet1!$AA$3000)

Under such benign conditions, SumIf might fire better.

2. If you can sort C13:AA3000 on column C in ascending order and
maintain the area sorted...

=IF(LOOKUP(C4,Sheet1!$C$13:$C$3000)=C4,
LOOKUP(C4,Sheet1!$C$13:$C$3000,Sheet1!$AA$13:$AA$3 000),
"")

will be enjoyably faster.

SteveC wrote:
=VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)


=SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$300 0)