Vlookup, multiple times in one column summing corresponding

[bstuart]

I have a list that contains common invoice numbers that appear multipl
times in column A and need to use the vlookup function to find and su
the corresponding amounts in column B.

As you would be aware the following vlookup functio
=VLOOKUP(A3,DATA,2,FALSE) will only return the one amount once i
reaches a match and I need it to continue down the column and sum al
matching invoice numbers.

Column A Column B
145768 356.87
145769 678.90
145880 80.87 *
145769 103.55
145770 56.90
145769 78.32
145880 54.09 *

The answer I’m looking to return is
145880 134.96

It has to be a vlookup style of function as I’m building multipl
sheets.

I did try Alan Beban's array functions VLOOKUPS (for returnin
multiple
lookup results) but it’s far too slow as I have thousands of invoic
numbers.

Brad
TIA :

 

[Frank Kabel]

Hi
a formula approach won't use VLOOKUP but SUMIF. Try
=SUMIF(B:B,account_number)

But if you want to create a report for all account numbers
try using a pivot table. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[AlfD]

Hi!

I'm not sure how much your wish to "build multiple sheets" gets in th
way of possible approaches.

Given that you can always revert at the end of a process to whateve
you had to begin with (if you plan to do it) would there be an
impediment to creating temporary sorted lists which could then b
operated on by Data>Subtotals? I know subtotals can be a bit slow, bu
sorting is quick.

After all, you _are_ looking for subtotals.

Al