help needed with lookup and related functions

[EdStevens]

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.

 

[Luke M]

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.

 

[EdStevens]

Perfect. A bit of adjustment for my actual layout (original post was a
simplified for clarity) and it worked perfectly. Thank you.

 

[T. Valko]

Try this...

Let's assume your lookup data is on Sheet1 in the range A2:K10. A1:K1 are
the column headers GrpA, GrpB, etc.

Names used in the formula:

Table: refers to Sheet1!$A$2:$K$10
Grps: refers to Sheet1!$A$1:$K$1

Enter this formula on Sheet2 B2:

=IF(COUNTIF(INDEX(Table,,MATCH(B$1,Grps,0)),$A2),"X","")

Copy across then down as needed.