Skip blanks and list

[KasperHH]

Hi there

I want to try and make my work a bit easier by making a list of drawings automatic populate sheets by marking them "x".

Drawings nr. in column H.

I would like a list as shown in column W for the 4.2 in column M. 

I tried if and xlookup, sort and other formulas but didn't succeed. Is it possible?

Regards Kasper

[#2 · Accepted solution]

@KasperHH 

=FILTER(H8:H14,M8:M14="x")

You can try FILTER function.

 

[#3]

@OliverScheurich 

Works perfect. Thanks.

[#4]

@KasperHH 

This may not be what you require, but it is possible to unpivot the entire array with a single dynamic array formula.

= LET(
    a?, mark="x",
    r,  IF(a?, rev,NA()),
    d,  IF(a?, dato, NA()),
    HSTACK(TOCOL(r,3,1), TOCOL(d,3,1))
  )