Adding a column if two ranges match

[xRai]

A B C D
1 Participant Volume Group 1 Group 2
2 name 1 3 name 3 name4
3 name 2 4 name 5 name 20
4 name 3 1 name 6 name 150
5 name ...
---------------------------------------------------------------
Sum Sum

I am attempting to add coulmn B based on which group the participants are
associated with.
I have a master list of the total number of participants for a month and
their contibuted volumes. I have 2 sublists for the group of which each
participant is a member. How do I find the volume each group contributed;
without going through each name?

 

[ck]

It might be easier if you create 2 working columns beside Group 1 & 2. e.g.
column D and F below:

A B C D E F
1 Participant Volume Group 1 Group 2
2 a 5 a 5 b 4
3 b 4 c 3 d 2
4 c 3 e 1 f 7
5 d 2
6 e 1
7 f 7 9 13


In D2, paste this =INDEX($B$2:$B$7,MATCH(C2,$A$2:$A$7,0))
In F2, paste this =INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$7,0))
Change the range accordingly.

Sum the numbers, in the example is 9 and 13.
See if this helps you.

 

[T. Valko]

Try this...

=SUMPRODUCT(--(ISNUMBER(MATCH($A2:$A10,C2:C4,0))),$B2:$B10)

Copy across

 

[xRai]

Thank you gentlemen, ck and T.Valko!

I used the solution provided by T.Valko as it fits in perfectly with my
application!
Works exactly how I had hoped

 

[T. Valko]

You're welcome. Thanks for the feedback!