extract only cells with data from a specific row in a 2D range and return it as an array

6fb924a9-b464-454a-9551-67336372547d.jpg

I have a 2D-range of 8 rows by 6 columns say L15:Q22
each row is 6 cells wide.. some cells contain text (colored in the picture above) and others are empty (no color).
cells with text are all to the left, while the empty cells are to the right as in the picture attached.

I have an extra column to the left of the 2D-range to use for lookup of the row number ( row selector ). I use this column cells in a dropdown menu for the user to select a value.

my objective is, when a user selects a value (which corresponds to a specific row in the 2D-range), I want to be able to get a variable length array containing only the cells with text from that specific row in the 2D-range .. i need this output array as an intermediary product to use in another formula somewhere else

I managed to get the relative row number of desired data in the 2D-range using match function match(Selector_Column, dropdown_menu_value,0)

then I used index to return the whole row with 6 cells as an array of 6 elements that contains text (corresponding to cells with data) and zeros ( for empty cells). The formula looks like this index(2D_range,result_of-match_above,0)

and the result i get is a fixed-size 1 by 6 array that looks like this
{"M4-item1","M4-item2","M4-item3",0,0,0}

my question is .. how can i get a smaller/filtered array of the above output array that contain only text, using index/match preferably with other formulae like row/column .. i.e .. the output should be an array that looks like this

{"M4-item1","M4-item2","M4-item3"} without the empty/zero elements

any ideas ?

=OFFSET(B1,result_of-match_above,0,1,COUNTIF(OFFSET(B1,result_of-match_above,0,1,6),"?*"))

Try that. If it doesn't work then please attach an actual file. It's easier than attaching a screenshot, and we can test with it. See yellow banner at the top of the page.