Use index, match, and row/column excel functions in a formula to return a subset array with just cells with text and exclude the empty ones

Question

I have this scenario where i want to extract a subset of a range in one row (using index, match and row functions preferably ) and return only smaller filtered array with cells that contain text/data and exclude empty cells .. I want to use this filtered array for further processing

The input 2-D array i want to get a subset array from is colored

The image shows the returned array size in last column

A dropdown menu is provided for the user to select the desired row (say A1:A6).

The row selected by the user determines which row of the 2-D colored range (say B1:G6 ) to extract the filtered array from then only cells with text/data are returned from that row

I tried the following code

I can get the array of whole correct row (all cells) using

index(B1:G6, match(A1:A6,value_from_dropdown_menue,0),0)

then i tried to apply row() function to filter out empty elements from that returned array .. lets refer to the returned_full_row_array as RFRA for readability

index(RFRA, row(indirect("1:"& counta(RFRA))))

the full formula looks like this (B8 is the cell with dropdown )

=INDEX(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0),ROW(indirect("1:"& counta(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0)))))

but the row() function is not returning an array and thus i don't any array returned .. i only get the first element.. even if i hardcoded row(1:3) .. i don't get 3 element array back ... only the first element of the the whole row

any ideas?

Edit : I can successfully get the whole row of my range using index match the result is a fixed size 1 by 6 array like this (example first and 2nd row ):

for 1st row >> {"M1-item1",0,0,0,0,0}
for 2nd row >> {"M2-item1","M2-item2","M2-item3","M2-item4",0,0}

desired output however should be a variable length array without the empty/zero elements :

for 1st row >> {"M1-item1"}
for 2nd row >> {"M2-item1","M2-item2","M2-item3","M2-item4"}

Answer 1

Given that the original question has changed, this is a much simpler problem. Let's say that we have some named ranges:

Named Range	Formula	Comment
FullRange	=L15:Q22	The full range of the data
MenuList	=K15:K22	The list of menu items
DropdownSelection	?	The cell with the dropdown list

This would be the formula to do the job:

=INDEX(
    FullRange,
    XMATCH(DropdownSelection,MenuList,0),
    XMATCH(
        SEQUENCE(
            ,
            MAX(
                MMULT(
                    SEQUENCE(,ROWS(FullRange),1,0),
                    MMULT((1-ISBLANK(FullRange))*(DropdownSelection=MenuList),
                    (SEQUENCE(COLUMNS(FullRange))<=SEQUENCE(,COLUMNS(FullRange)))*1)
                )
            )
        ),
        MMULT(
            SEQUENCE(,ROWS(FullRange),1,0),
            MMULT(
                (1-ISBLANK(FullRange))*(DropdownSelection=MenuList),
                (SEQUENCE(COLUMNS(FullRange))<=SEQUENCE(,COLUMNS(FullRange)))*1
            )
        ),
        0
    )
)

Sorry it's so ugly in the middle. It uses ISBLANK to find which cells are used.

In particular, this part:

SEQUENCE(COLUMNS(FullRange))<=SEQUENCE(,COLUMNS(FullRange)))*1

produces an upper triangular matrix.

If we have a matrix that contains this:

(1-ISBLANK(FullRange))*(DropdownSelection=MenuList)

it will contain only those cells we actually want.

By taking the matrix product, we get a matrix with 0's everywhere except on the row we're after. On the row we're after, it has 1 on the first cell we want up to the 2nd cell, 2 on the second cell we want up to the third, and so on. By XMATCHing from the left (going right), it will only match on those cells we want.