Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup in excel have alternative result than #N/A selectable | Excel Worksheet Functions | |||
Vlookup Alternative Needed | Excel Discussion (Misc queries) | |||
Sumproduct and Vlookup | Excel Discussion (Misc queries) | |||
Nested Vlookup or alternative? | Excel Worksheet Functions | |||
VLOOKUP in SUMPRODUCT array | Excel Worksheet Functions |