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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top