Sum up similar values in VLOOKUP/INDEX-MATCH excel functions

1
votes

I have an issue with a vlookup/index-match formula in excel 2010.

I have a table that contains duplicate values in the name column. I have a table in another sheet that reference the name & amount from the first sheet. in case a duplicate name exist in the original table, it should sum the amount.

For example, look at the pic below. ABC is a duplicate name, and it's amount should be zero. How can I use Vlookup/index-match formulas in order to display "0" in the result when I lookup the name ABC?

In the destination sheet, the name only appears once, hence I don't want to add another ABC to the dest table.

Example of the problem Thanks ! Roni.

1
excelexcel-formulaexcel-2010vlookup
I'd suggest using Pivot tables – Peter L.
The originated table IS a PIVOT table. I didn't add the whole column. Because there's another SECTOR column, the ABC value exist in two sectors. – Roni Vered
But if it already is a pivot table, why not simply add another one (ie copy this one) and take out the sector column? – Peter Albert

1 Answers

8
votes

VLOOKUP and or INDEX/MATCH will only return you one entry (the first match) - so you cannot use it for summing (unless in an array formula).

However, using SUMIFS (or in Excel 2003 and earlier SUMIF) will be just what you need: So In F2, you could write: 

=SUMIFS($C$2:$C$6,$B$2:$B$6,E2)