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

Advertisements

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.
 
Ad

Advertisements


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