S
SteveC
=VLOOKUP(C44,'Sheet1'!$C$13:$AA$3000,25,FALSE)
=SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$3000)
=SUMPRODUCT(--($D13='Sheet1'!$D$13:$D$3000),'Sheet1!AA$13:AA$3000)
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
SamuelT
What do you mean by faster? Processing wise/typing wise
B
Bob Phillips
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)
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)
G
Guest
I reckon this is one for Harlan!
ANdy.
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)
B
Bob Phillips
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)
....
--
HTH
Bob Phillips
(replace somewhere in email address with googlemail if mailing direct)
B
Biff
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
.....and, if the lookup table is sorted ascending, Vlookup is significantly
faster than if the table is not sorted.
Biff
A
Aladin Akyurek
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.
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.