CODEWITHSUNDEEP
excelexcel-formula
James
James

Reputation: 33

Output a list if month row and columns match

I have a table with month as per below

Excel Snapshot

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.

Upvotes: 1

Views: 336

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27363

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)))

FILTER FUNCTION -- APPLICABLE TO EXCEL 2021 & O365 USERS

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27363

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

enter image description here

Upvotes: 1

Related Questions