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?
|