Formula in B2:
=IF(ISNUMBER(MATCH($A2,OFFSET(Sheet1!$A$1,1,MATCH( B$1,Sheet1!$1:$1,0)-1,20,1),0)),"X","")

Copy down and across as needed. Note that the 20 in formula should be equal
to or greater than largest Group on Sheet1.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"EdStevens" wrote:

Excel 2003

Given two worksheets in a .xls file.
First worksheet is lookup data. First row of each column is a group name,
say GrpA, GrpB, GrpC. Under each group name (rows 2 thru n) are names of
people, say Tom, Dick, Harry. GrpA may have 5 names (rows 2 to 6) and GrpB
may have 10 names (rows 2 to 11), etc. So we have say:

A1 = GrpA
A2 = Tom
A3 = Dick
A4 = Harry

B1 = GrpB
B2 = Jane
B3 = Sally

Second worksheet is working data. First column is list of names, starting in
row 2. Columns B thru Z have a group name in row 1 -- GrpA, GrpB, etc --
matching the Group names in the lookup worksheet. A group name in row 1 may
appear in multiple columns. So we may have

A1 = "Name"
A2 = Tom
A3= Dick
A4 = Harry

B1 = "GrpA"
C1 = "GrpB"
D1 = "GrpC"
E1 = "GrpA"

Some of the names in the working data (Col A) will appear under more than
one group in the lookup data.

Some of the names in the working data will appear under exactly one group in
the lookup data.

Some of the names in the working data will appear under no groups in the
lookup data.

The task is to set a formula in the working data, at the intersection of a
name and a group, that will place an "X" in the cell if the name in that row
appears in the column of the matching group in the lookup sheet.

I've been playing around with IF, the various LOOKUP functions, and the
various IS* functions, but have not come up with something that works.