Lookup Across Multiple Columns - Return Category names
 

I'm trying to figure out a formula for this scenerio:

col a col b col c col d col e col f
col g
name cat1 cat2 cat3 cat4 cat5
cat6
Smith, John x x x 1 x
x
Taylor, Mary 1 x 1 x x
x
Adams, Ted x 1 x x 1
x

"X" meaning the cell is blank

Is there a formula that will lookup Taylor,Mary & return all the category
names ("cat1, cat2, cat3, etc) where there is a number "1" showing?

Lookup Across Multiple Columns - Return Category names
 

Try this...

Assume your table is in the range A1:G4

A10 = lookup name = Taylor,Mary

Enter this formula in A9. It will return the count of ones for the name of
interest.

=SUM(INDEX(B2:G4,MATCH(A10,A2:A4,0),0))

Enter this array formula** in B10 and copy across to G10:

=IF(COLUMNS($B10:B10)$A9,"",INDEX($B$1:$G$1,SMALL (IF(INDEX($B$2:$G$4,MATCH($A10,$A$2:$A$4,0),0)=1,C OLUMN($B$1:$G$1)),COLUMNS($B10:B10))-COLUMN($B$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"slf" wrote in message
...
I'm trying to figure out a formula for this scenerio:

col a col b col c col d col e col
f
col g
name cat1 cat2 cat3 cat4 cat5
cat6
Smith, John x x x 1
x
x
Taylor, Mary 1 x 1 x
x
x
Adams, Ted x 1 x x
1
x

"X" meaning the cell is blank

Is there a formula that will lookup Taylor,Mary & return all the category
names ("cat1, cat2, cat3, etc) where there is a number "1" showing?

Lookup Across Multiple Columns - Return Category names
 

Excel 2007
Two more methods:
PivotTable (no formulas)
Tables (with macro)
http://www.mediafire.com/file/1ymtjm...07_13_09a.xlsm