Lookup Across Multiple Columns - Return Category names

  • Thread starter Thread starter slf
  • Start date Start date
S

slf

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?
 
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,COLUMN($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.
 
Back
Top