Develop/Extract List?

[beachfarmer]

Greetings:

I would like to develop a list/extract data from the list of courses (column a) below from the text designation in column c (taken).  As such, I'm looking for a formula to insert in the cell(s) under the headings below the table for Summer 2022 (Su22), Fall 2022 (F22) and Spring 2023 (S23).   So, based upon the proper formula(s) to be put under each header, the courses listed under the Summer 2022 would be Estimating II 3 (for credits); under Fall 2022 would be Construction Law 3, Mechanical Electrical 3, Quality Management and under Spring 2023 would be Construction Graphics 3.

I don't necessarily need the formula to add rows below the headers....there will never be more than 5 or 6 courses (rows) under each header and I can replicated the formula, etc

Any help is greatly appreciated...thanks...John

Course	Credits	Taken
Structural Analysis	3	x
Estimating II	3	Su22
Construction Law	3	F22
Mechanical Electrical	3	F22
Construction Graphics	3	S23
Construction Surveying	2	x
Quality Management	3	F22
Summer 2022
Course	Credits
Fall 2022
Course	Credits
Spring 2023
Course	Credits

[#2 · Accepted solution]

@beachfarmer 

Maybe as shown in the attached file. I entered the formula below and it seems to work in my sheet.

=INDEX(A$2:A$8,SMALL(IF($C$2:$C$8="SU22",ROW($A$1:$A$7)),ROW(A1)))

Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

[#3]

@OliverScheurich 

thank you!...this works!  Can I put another twist on my problem?

The native format of my data is shown below where there are two different "columns of information" (my first example is a single "column of information" whereby I have to cut the second "column of information" and past it under the first "column of information" to complete the analysis with the formula you provided.  

Is there a formula possible that will search both "columns of information" and list the info under the headers just is in the first example I had?...thanks again...John 

Course	Credits	Taken		Course	Credits	Taken
Structural Analysis	3	x		Construction Graphics	3	S23
Estimating II	3	Su22		Construction Surveying	2	x
Construction Law	3	F22		Quality Management	3	F2
Mechanical Electrical	3	F22
Summer 2022
Course	Credits
Fall 2022
Course	Credits
Spring 2023
Course	Credits

[#4]

@beachfarmer 

I don't have a formula to do that. I think you did absolutely the right thing to copy the two tables under each other. Before creating formulas in Excel, one should always prepare the data in such a way that the simplest and most efficient evaluation possible can be achieved.