Jul 30 2021 05:36 PM
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.
Jul 30 2021 08:35 PM
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")))
Oct 18 2022 04:27 PM
@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.
Oct 18 2022 09:01 PM
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: