I have a table with month as per below

In another worksheet, a column will display a list (please refer to cell R2:R11 in the snapshot) of part number that matches the selected month and content (that contain partial text).

Very much appreciated to share with me the method. Thanks.

CodePudding user response：

Have you tried using in this way, i hope it should work for you as per your expected output, kindly refer image below, so here are two alternative ways

Formula used in cell R2 =IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($C$2:$N$15)-ROW($C$2) 1)/(($P$2=$C$1:$N$1)*(LEFT($C$2:$N$15,2)=LEFT($Q$2,2))),ROW(A1))),"")

Formula used in cell S2 =IFERROR(INDEX($A$2:$A$15,SMALL(IF((($P$2=$C$1:$N$1)*(LEFT($C$2:$N$15,2)=LEFT($Q$2,2))),ROW($C$2:$N$15)-ROW($C$2) 1),ROW(B1))),"")

The second formula requires to confirm press CTRL SHIFT ENTER after entering formula if not using O365

CodePudding user response：

And if you are using O365 then you use FILTER FUNCTION as shown below in the image

Formula used in cell R2 =FILTER($A$2:$A$15,INDEX(($P$2=$C$1:$N$1)*(LEFT($Q$2,2)=LEFT($C$2:$N$15,2)),,MATCH($P$2,$C$1:$N$1,0)))