Formula to display row header when cell contains text

Question

Level 1

12 points

Formula to display row header when cell contains text

Hello I am after some help in numbers.

You can see from the table below I have names in the row header and sports across the column headers.

My finished table will be much larger but I have tried to simplify it here.

What I want to do is create a formula that will take the name of the person in column A when there is the letter Y in the intersecting cell.

For example in the Football column where Y intersects Football and Simon, at the bottom I have added Simon to that column

Rugby column I have added Simon and Henry

In excel I used an array formula for this.

One potential problem is that in order to create these lists is that if a cell is blank I would like it to skip that cell.

So if I have a table of 300 names, I do not want the list created to have gaps so that the list is 300 rows long.

Eg in the rugby and hockey columns Simon and Henry do not have a cell space between them (this cell space would refer to john).

Hope it makes sense.

MacBook Pro, OS X El Capitan (10.11)

Posted on Apr 13, 2016 7:51 AM

Reply

Answer 1

t quinn

Level 5

7,063 points

Apr 13, 2016 9:03 AM in response to mrcoates14

Hi mrcoates,

The only way I can think of to duplicate the array function is with additional columns. Iwould be addinga column for each sport. these could be hidden. Numbers makes it easy to have multiple tables so I will create a report table that can stay with the original or cut and pasted anywhere in the doc.

The formula in F2 =IF(B2="y",B$1&COUNTIF(B$1:B2,"Y"),"")

This is filled down and across. You would have columns H and I also.

A2 =INDEX(Table 1::$A,MATCH(A$1&ROW(cell)−1,Table 1::F,0))

This can be wrapped in IFERROR() once you have it working. It is filled down and across.

quinn

Reply

Answer 2

Barry

Level 9

60,023 points

Apr 13, 2016 11:13 AM in response to mrcoates14

Hi mrcoates,

My approach would be similar to t quinn's, but I'd use numbers without the attached text in the index columns, and revise the second table to avoid the error e generating the error messages.