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$3000)

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

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" <SteveC@discussions.microsoft.com> wrote in message
news:09E0DB0B-A45E-41CC-A167-3803D1598492@microsoft.com...
> =VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)
>
>
> =SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$3000)
>

I reckon this is one for Harlan!

ANdy.

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:OGyGs2AgGHA.3900@TK2MSFTNGP05.phx.gbl...
> 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" <SteveC@discussions.microsoft.com> wrote in message
> news:09E0DB0B-A45E-41CC-A167-3803D1598492@microsoft.com...
>> =VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)
>>
>>
>> =SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$3000)
>>
>
>

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 news:%23AlzwABgGHA.4776@TK2MSFTNGP05.phx.gbl...
> I reckon this is one for Harlan!
>
> ANdy.
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:OGyGs2AgGHA.3900@TK2MSFTNGP05.phx.gbl...
> > 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" <SteveC@discussions.microsoft.com> wrote in message
> > news:09E0DB0B-A45E-41CC-A167-3803D1598492@microsoft.com...
> >> =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

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

Biff

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:OGyGs2AgGHA.3900@TK2MSFTNGP05.phx.gbl...
> 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" <SteveC@discussions.microsoft.com> wrote in message
> news:09E0DB0B-A45E-41CC-A167-3803D1598492@microsoft.com...
>> =VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)
>>
>>
>> =SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$3000)
>>
>
>

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.

SteveC wrote:
> =VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)
>
>
> =SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$3000)
>