Lookup with multiple matches

[Gary]

Hi,

I have a rather unusual problem here... Excel 2007. I also have Access
2007 if it would be better/helpful...

I have a spreadsheet containing all my volunteers' names, and the committees
they serve on (along with contact info, etc.). It's actually a
copy-and-paste from Outlook - the committte names are Outlook categories...

What I want to do is use the lookup function (or another way) to generate a
chart which would have my committees across the top, the members' names down
the side, and check marks showing which committee each member is on. For
example, it would say something like:

Board Executive Committee Strategic
Planning Committee
Tom x x
Joe x
x
Ralph x
x

So I need to look up Tom and see if Tom has the Board category - if so,
place a X there, if not, don't. Then see if Tom has the Exec category - if
so, place an x; if not, don't.

Any help would be appreciated!!

 

[Jim Thomlinson]

A couple of ways to do this.

1. Concatenate the name and committee together to make a unique identifier
in your source data. You can then just use a countif function to determine if
an instance of the name and committee exist. My XL is tied up at the moment
so this is off the top of my head but something like this would do

=if(countif($A2 & B$1, Sheet2!($A$1:$Z$1000)) > 0, "X", "")

2. Use a pivot table. Select your source data. Select Data -> Pivot Table ->
Follow the wizard. Place the committees across the top and the names down the
ledft side. Place th committees in the Data area and you will get exactly
what you wanted except that instead of X you will get 1's or 0's.

 

[Gary J. Jahnke]

Jim,

Your Pivot Table suggestion worked beautifully! No problem with 1's or
Zeros - it actually gave me 1 or blank field - which is just fine.

Thanks,

Gary