Return Header if specified row contains a value

[Powkiwi90]

I have a data set that looks like this:

A B C D E F
P 2 0 2 0 0 3
Q 0 3 1 1 1 0
R 6 0 0 0 1 0
S 0 5 0 3 0 0
T 0 0 5 0 0 2
U 0 0 7 0 0 0


I need to be able to search that table by putting selecting an input value from P-Q then if that selected row has a value greater than 0.
I need it to return any other rows that also have a non-zero value in that column, how many of the columns share a non-zero value and which ones they are.

So for example:

Value:
P
Result :
Q - 1 - C
R - 1 - A
T - 2 - C, F

(another way of thinking would be P shares 1 similar column with Q which is C, 1 similar column with R which is A and 2 similar columns with T which are C and F)

Value:
R
Result :
P - 1 - A
Q - 1 - E

Value:
S
Result:
Q - 2 - B, D

Ideally these three results would be in separate cells if possible. So the result for the last value, S, would look like | Q | 2 | B, D |

If I can get that going, that would be most amazing. I thought a pivot table might be the way, but its not really going at all.

Many many many thanks in advance!

 

[Powkiwi90]

Just realized i could have made that more simple. The numbers in the cells of the table are unimportant. If need be i could replace any numbers with 1.

 

[Marcelo Branco]

You need complex formulas to accomplish what you want. Something like this

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

1​

A​

B​

C​

D​

E​

F​

Criteria​

Results​

Count​

Header1​

Header2​

Header3​

Header4​

Header5​

Header6​

2​

P​

2​

0​

2​

0​

0​

3​

P​

Q​

1​

C​

3​

Q​

0​

3​

1​

1​

1​

0​

R​

1​

A​

4​

R​

6​

0​

0​

0​

1​

0​

T​

2​

C​

F​

5​

S​

0​

5​

0​

3​

0​

0​

U​

1​

C​

6​

T​

0​

0​

5​

0​

0​

2​

7​

U​

0​

0​

7​

0​

0​

0​

<tbody>
</tbody>

Criteria in I2

Array formula in J2 copied down till J6
=IFERROR(INDEX($A$2:$A$7,SMALL(IF(MMULT((INDEX($B$2:$G$7,MATCH($I$2,$A$2:$A$7,0),0)>0)*($B$2:$G$7>0)*($A$2:$A$7<>$I$2),{1;1;1;1;1;1})>0,ROW(A$1:A$6)-ROW(A$1)+1),ROWS(J$2:J2))),"")
Ctrl+Shift+Enter

Regular formula in K2 copied down
=IF(J2="","",COUNTIFS(INDEX($B$2:$G$7,MATCH($I$2,$A$2:$A$7,0),0),">0",INDEX($B$2:$G$7,MATCH(J2,$A$2:$A$7,0),0),">0"))

Array formula in L2 copied across till Q2 and down
=IFERROR(INDEX($B$1:$G$1,SMALL(IF(INDEX($B$2:$G$7,MATCH($I$2,$A$2:$A$7,0),0)>0,IF(INDEX($B$2:$G$7,MATCH($J2,$A$2:$A$7,0),0)>0,COLUMN($B$1:$G$1)-COLUMN($B$1)+1)),COLUMNS($L2:L2))),"")
Ctrl+Shift+Enter

Hope this helps

M.