# Solved Return value for multiple lookup

Microsoft Ms sla excel lic/sa 2yr-1
August 12, 2009 at 18:34:39
Specs: Windows Vista
 Hello,I have a worksheet that have dates corresponding to \$ amounts. I want to be able to match a date and sum up the amounts for that date (e.g. if cell A11 = 12Aug2009, add all \$ that matches 12Aug2009. Here's my worksheet: AB1 Shipping 2 12-Aug-09 \$10.003 13-Aug-09 \$20.004 14-Aug-09 \$30.005 12-Aug-09 \$40.006 12-Aug-09 \$50.007 13-Aug-09 \$60.008 14-Aug09 \$70.009 10 11 12-Aug-09 \$280My formula:=SUM(INDEX(\$A\$2:\$B\$8,SMALL(IF(\$A\$2:\$A\$8=\$A\$11,0),ROW(2:2)),2,1))result is \$280, (correct answer if my formula is right is 12Aug \$10 + 12Aug \$40 + 12Aug \$50 = \$100)problem (1) my formula adds all date matches, not those that meet the criteria (if A11 = 12aug2009)problem (2) I actual worksheet have other columns in the same worksheet like the one below:ABCDE1 Shipping Supply Rm 2 12-Aug-09 \$10.00 12-Aug-09 \$10.003 13-Aug-09 \$20.00 13-Aug-09 \$20.004 14-Aug-09 \$30.00 14-Aug-09 \$30.005 12-Aug-09 \$40.00 12-Aug-09 \$40.006 12-Aug-09 \$50.00 12-Aug-09 \$50.007 13-Aug-09 \$60.00 13-Aug-09 \$60.008 12-Aug-09 \$70.00 12-Aug-09 \$70.0091011 12-Aug-09 \$560My formula:=SUM(INDEX(\$A\$2:\$B\$8,SMALL(IF(\$A\$2:\$A\$8=\$A\$11,0),ROW(2:2)),2,1))+SUM(INDEX(\$D\$2:\$E\$8,SMALL(IF(\$D\$2:\$D\$8=\$A\$11,0),ROW(2:2)),2,1))result is 560, (correct answer if my formula is right is SHIPPING: 12Aug \$10 + 12Aug \$40 + 12Aug \$50 = \$100 ANDSUPPLY RM: 12Aug \$10 + 12Aug \$40 + 12Aug \$50 = \$100TOTAL \$200.00Thank you for your assitance.Sincerely,Lex

See More: Return value for multiple lookup

#1
August 12, 2009 at 19:55:00