Populate dynamic list of row or column items based on table value.

Occasional Visitor

joshua935_3-1627690975042.png

 

 

 

Hi everyone, 

I am trying to generate two lists based on a "y" or "n" value in the table. The image above shows my desired result in red box. any help would be great.

3 Replies

@joshua935 

Maybe this...

 

In H4

=SORT(FILTER(B5:B8,INDEX(C5:E8,,MATCH(H$3,$C$4:$E$4,0))="y"))

 

In J4

=SORT(TRANSPOSE(FILTER(C4:E4,INDEX(C5:E8,MATCH(J$3,$B$5:$B$8,0),)="y")))

 

 

@Subodh_Tiwari_sktneer Thank you so much for sharing the formula.

 

However, for me it isn't working if C4, D4, E4 contains date value.

 

How do I convert date or do I have to try different formula at H4 and J4?

 

Thanks.

Hi @joshua935 

 

Alternatively in H4

=FILTER(B5:B8, XLOOKUP(H3, C4:E4, C5:E8, 0) = "y", "No match")

in J4

=TRANSPOSE(FILTER(C4:E4, XLOOKUP(J3, B5:B8, C5:E8, 0) = "y", "No match"))

 

Re. it isn't working if C4, D4, E4 contains date value No good reason:

_Screenshot.png