# Lookup Across Multiple Columns - Return Category names

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?

T

#### T. Valko

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.