Reverse index match with multiple outputs

Question

Given a matrix like the one shown below, I would like to be able to input a person's name and output every group the person is in.

Example matrix

I am OK with the reverse index match lookup but do not know how to amend the formula to produce an array that works. I have tried using SMALL/LARGE function but can't get it to work.

My formula for CELL C15 is

=INDEX($C$2:$I$2,,MATCH("ü",INDEX($C$3:$I$12,MATCH($B$15,$B$3:$B$12$,0),),0))

Any ideas?

Can you check the post. I think it same as your question. How to get header row data related to left column name (whose data are not empty) — Harun24hr
– Harun24hr, Commented Jul 24, 2018 at 10:44

Daniel Souza · Accepted Answer · 2018-07-24 15:28:04Z

2

Use this formula in your C15 and pull down until you need (close with ctrl+shift+enter)

=IFERROR(INDEX($C$2:$I$2,SMALL(IF(INDIRECT("C"&MATCH($B$15,B:B,0)&":I"&MATCH($B$15,B:B,0))<>"x",TRANSPOSE(ROW($1:$7)),""),ROW(A1))),"")

If you need to get the reverse result (list of people by group). Do the same with:

=IFERROR(INDEX($B$3:$B$12,SMALL(IF(INDIRECT(ADDRESS(3,MATCH($B$15,$2:$2,0))&":"&ADDRESS(12,MATCH(B$15,$2:$2,0)))="v",ROW($1:$10),""),ROW(A1))),"")

edited Jul 24, 2018 at 15:28

answered Jul 24, 2018 at 13:15

Daniel Souza

4305 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jordan Todd Over a year ago

Thank you that works perfectly. How would you adjust the INDIRECT so that it worked in reverse, i.e Given a group, output the individuals?

Daniel Souza Over a year ago

I updated my answer with a formula you could use to get the list of persons by inputing the group (assuming the same cells)

Collectives™ on Stack Overflow

Reverse index match with multiple outputs

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related