Using a Formula to return a list based on a cellls value.

I am looking for a formula to return the data from rows b to k into the table (row m) based of cell M6.

if for example Ks4 Biology is selected in m6 I would like the table to populate with all the rows from that start with Ks4 Biology.
you will see that in the example document column B is the same as column M in the table I want the data to show. I would like all of the columns to copy over.

(this is part of a much larger dashboard but I am struggling with returning a list).

Any help on this is much appreciated.

Thanks a lot

In M14

=IFERROR(INDEX(B$2:B$117,SMALL(IF($A$2:$A$117=$M$6,ROW($A$2:$A$117)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+Enter

Copy across and down

Ensure entry in M6 matches entries in Column A (M6 had additional full stop in your example)

Change 117 in range as required

JonWilf,

Attached has a "Range" of your subjects in Col X which populate the DropBoxc in N5,

N5 - Select the subject you want

Data appears in M14 - V31 using "Array" formula

{=IF($N$4="","",IFERROR(INDEX($A$1:$K$19000,SMALL(IF($A$1:$A$19000=$N$4,ROW($A$1:$A$19000)),ROW($A1)),2),""))}

(If N4 is blank, cell remains blank. If you select a subject in N4, copy all the data from Col B where Col A equals your choice.)

As you can see, that is copied across the first row, adjusting the "2" to "3", etc, and then the row copied down.

Hope this helps

Ochimus