What is the Excel formula to filter these three lists?

Question

What I am trying to accomplish:

Source Data:

	A	B	C	D	E	F	G	H	I	J	K	L	M
List A	30	0	0	14	0	0	9	0	0	17	0	0	6
List B	17	13	0	0	12	22	21	0	8	0	0	0	7
List C	31	0	0	5	42	1	0	0	0	0	0	0	41

Output:

List A	D	G	M
	14	9	6
List B	B	E	I	M
	13	12	8	7
List C	D	F
	5	1

How would I filter my three lists so that it pulls the letter and corresponding number if the number is greater than 0 but less than 15?

Answer 1

Using Recursive REDUCE() function:

---

=LET(
     _Data, A2:N4,
    IFNA(DROP(REDUCE("",SEQUENCE(ROWS(_Data)),LAMBDA(a,b,
    LET(c,CHOOSEROWS(_Data,b),d,DROP(c,,1),
    VSTACK(a,IFNA(HSTACK(@c,FILTER(VSTACK(B1:N1,d),(d>0)*(d<15))),""))))),1),""))

---

_{CAVEAT: It is highly suggested not to use the whole empty ranges in the formulas, this is because the functions will be iterating through the empty cells thus making it slower slower and slower, thus affecting the performance.}

---

Answer 2

A bit simpler solution for one list at a time (a formula for B9):

=LET(letters,$B$3:$N$3, data,INDEX($B$4:$N$6,MATCH(A9,$A$4:$A$6,0),),
     FILTER(VSTACK(letters,data),(data>0)*(data<15)))