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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


=SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$3000)
 
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)
 
I reckon this is one for Harlan!

ANdy.

Bob Phillips said:
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)
Click to expand...
 
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)
 
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
 
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$3000),
"")

will be enjoyably faster.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combination IF- and VLOOKUP-function fails 2
How do I use case sensitive VLOOKUP? 8
Vlookup and If statement help 1
move data w/o blank data to another sheet 1
move blank data 10
Vlookups to return a N/A value 3
Conditional sum, when condition has multiple values 3
SUMPRODUCT- double unary vs. * 2

Back
Top