Excel Filter function question

Question

I am using the below filter function the display a list of names (D4-D31) that have cells with things in them (Z4-KD31)

Is there a way to get the list result to show the name in (D4-D31) with the latter in the cells (Z4-KD31)

Example Bob is in D4 in AA4 is a T and Jim in D5 with AA5 is A. I would like the list to say Bob-T then Jim-A. Currently it just says Bob, Jim.

=FILTER($D$4:$D$31, INDEX($Z$4:$KD$31, 0, MATCH(CU$107, $Z$3:$KD$3, 0))<>"", "")

Answer 1

Try this in CU105:

=LET(w, INDEX($Z$34:$KD$60, 0, MATCH(CY$104, $Z$33:$KD$33, 0)), FILTER($D$4:$D$27&"-"&w, w<>"", "")

Answer 2

=LET(w, INDEX($Z$4:$KD$27, 0, MATCH(CY$104, $Z$3:$KD$3, 0)), FILTER($D$4:$D$27&"-"&w, w<>"", "")

Answer 3

I link to a strip down version.

https://docs.google.com/spreadsheets/d/1ud-z82yMqtxQKzqdrL30LTpdilOLelBI/edit?usp=sharing&ouid=102210349829605650097&rtpof=true&sd=true

Answer 4

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 5

In order to be sure that the data in the rows is kept together appropriately, the entire range will need to be included in the FILTER function. You can then use CHOOSECOLS to return only specific columns from the array.

=CHOOSECOLS(FILTER(D4:AA8,Z4:Z8<>""),1,24)

Note that I replaced the INDEX/MATCH portion of the formulas with a simpler criterion since the whole spreadsheet isn't in front of me.