I hope I can explain what I need to do. This may not be possible in Excel, but I think it can be done, I just don't know how to do it. It may need an Index or Match formula or both. Thank you in advance for any help you can give me.

I have a list of names in col. A. Col B is what the name owns. Col. C is how many they own. There are only 7 "colors" that they can own - Red, Blue, Green, Black, Pink, Yellow, & Purple. I have these colors listed as headers in Row 1. For example, D1=Red, E1=Blue, F1=Green...J1=Purple. I need to match the row that has the two names and put the quantity of each color under the corresponding header.

So, using the example below, I need to match "Adams" with "Adams", then return the amount of "Red"s owned (3) by Adams under the header "Red".

If Adams-Adams begins row 2, in cell D2 I need it to lookup "Adams" in column A, then if that is a match, lookup up D1 (which is Red) and find it next to Adams in Column B, then return the number 3 from column C. Then, I need to copy that formula across so it looks up Adams then Blue and returns the number of Blues that Adams owns, and so on. When it gets to the cell header Yellow, it will return an #N/A since Adams doesn't own any Yellows.

Once I get the formula correct across columns D, E, F, G, H, I, and J, then all I need to do is copy the formulas and paste next to each name in my list (which is quite long).


---A--------B----C------D-------E--------F-------G-------H--------I----------J
------------------------Red----Blue---Green--Black---Pink---Yellow---Purple
Adams-Adams--------3--------2--------3------n/a-----n/a-----n/a-------n/a
Adams-Red-3
Adams-Blue-2
Adams-Green-3

Anderson-Anderson-n/a-----2-------1--------1-------3-------n/a--------n/a
Anderson-Blue-2
Anderson-Green-1
Anderson-Black-1
Anderson-Pink-3

Arnold-Arnold----------2-------n/a-----n/a-----1-------1--------2-----------3
Arnold-Red-2
Arnold-Black-1
Arnold-Pink-1
Arnold-Yellow-2
Arnold-Purple-3