Using a lookup or index match function to sum list of values with different reference names

[vijaykumar]

Hello, I need to figure out a way to match a list of accounts (first table below) to a list of family names (second table below) and consolidate (sum) the values from accounts belonging to members of the same family (indicated by accounts with the same family name in table 1 such as Wallis and Baldwin). I want to pull these values into a worksheet that already has a column with the family names (table 2 below) however this sheet only has one line for each family, so if that particular family has more than one account on the source page, I'll need it to sum those values and pull the single total into the sheet. I will constantly be adding new names and rows to the sheet over time, so want to ensure the formula will still work in this case. Please note the client's last name will not always be a part of their Account Name in table 1 below. I do have a complete list of account names and the corresponding family names.

Source Sheet (Sheet I'm pulling values from, in a different workbook)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Jack Wallis Trust[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]Lane Wallis Savings Account[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Anthony Wallis Checking[/TD]
[TD]$150[/TD]
[/TR]
[TR]
[TD]Jeff Bross Personal[/TD]
[TD]$125[/TD]
[/TR]
[TR]
[TD]Alex Baldwin LP Account[/TD]
[TD]$140[/TD]
[/TR]
[TR]
[TD]Jess Baldwin Personal[/TD]
[TD]$70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]$635[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Desired output Sheet I'm pulling values into)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Wallis[/TD]
[TD]$300[/TD]
[/TR]
[TR]
[TD]Bross[/TD]
[TD]$125[/TD]
[/TR]
[TR]
[TD]Baldwin[/TD]
[TD]$210[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]$635[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
Vijay

 

[JayBurn]

You can use "*"& [Your Short Name cell ref] &"*" with a SUMIF;
=SUMIF(Table1!A:A,"*"&Table2!A1&"*",Table1!B:B)