Finding Multiple Instances in a List

[stacy.mcmillan]

Hello...

I am having a problem with the VLOOKUP function. I may not be able to
use it the way I am trying to, and hopefully someone can give me some
direction.

I have data in two main columns, A and B. In column A, I have a list
of Functions, many of which are repeated several times down the
column. In column B, I have a list of Teams, again of which many are
repeated. Basically, many Teams can share any given Function, thus
causing the values to be listed more than once.

In column C, I have only the unique Function values listed, meaning
that each one only appears once. I also have the Team names list in
Row 1 across starting at column D1, and progressing through M1.

What I am trying to do is to take the the first value in colun C, find
it in column A, then look at the value in column B... If the value in
column B is equal to the value in D1 lets say, place an 'X' in the
cell. The problem I am running into is that my formula is stopping
after it finds the first instance. So for example, I can get it to
place an 'X' under the first Team name, as long as that Team name is
the first one it finds.

=IF(VLOOKUP(C4,A4:B12,2,FALSE)=D3,"Yes","No")

What I need it to do is basically look at all instances the Function
value occurs in column A, and match them up (put 'X's) to all of the
corresponding Teams.

I realize this was not written very well, but any help will be greatly
appreciated!! Thanks in advance.

 

[T. Valko]

Try this entered in D2:

=IF(SUMPRODUCT(--($A$2:$A$100=$C2),--($B$2:$B$100=D$1)),"x","")

Copy across then down as needed. Adjust for the correct end of range.