Need a Macro for two sheets, matching and looping, to copy data over.

Hello, new to online excel forums - hoping someone can help. I’m assuming this will have to be a macro, but unsure how to make this loop work for accurate data extraction.

Need to find a match between two cells on two sheets. Once that occurs, I need to extract data with specific values from Sheet1 over to Sheet2. The number of rows that data each match will occupy on Sheet1 vary, so I need Excel to look for data to copy over beginning the next row down in Column A, and continue to go to the next cell row down in Column A until one of two specific values comes up – then it then needs to stop.

Sheet1 Screenshot:


1) Sheet1 is raw data from a csv file, the material is sorted by a STUDENT_ID number in Column M. The number of rows tied to this STUDENT_ID number will vary by how much information our clients give us, therefore I cannot set a specific range of rows to search within.

Sheet2 Screenshot:

2) Sheet2 is where I'm pulling all needed information from the first sheet, into one row assigned with the same STUDENT_ID number in Column K.

I need to match the STUDENT_ID number from Sheet2, Column K to the same STUDENT_ID number on Sheet1, Column M.
Once the STUDENT_ID numbers match, I need excel to begin a search in 1) Sheet1, Column A, in the VERY NEXT row down from where it located the match.
For example, if STUDENT_ID “7" is matched on Sheet1, M13, I need for it to begin looking for a specific term ‘NOTEORDER’ in Sheet1, A14. If it is a match, I need three items to copy over to Sheet2:
1) The term ‘NOTEORDER’ in Column A to pull over to Sheet2, Column L (L8 in this example).
2) The data from the same row as ‘NOTEORDER’ from Sheet1, Column E (E16 in this example), move it to Sheet2, Column L (L8 in this example).
3) The data from the same row as ‘NOTEORDER’ from Sheet1, Column F (F16 in this case), move it to Sheet 2, Column M (M8 in this case).
If the cell in Sheet1, Column A isn’t a match, I need it to go down to the next row in Sheet 1, Column A, and continue this search until the term ‘PARTICPANT’ comes up, OR the cell is Blank - then it needs to stop searching.

Hopefully someone has an idea for a looping macro on this? I’m stumped. Much appreciated in advance!

On Sheet2, in cell L2, enter the formula

=IFERROR(INDEX(OFFSET(Sheet1!$A$1,MATCH($K2,Sheet1!$M:$M,FALSE)-1,0,MATCH($K3,Sheet1!$M:$M,FALSE)-MATCH($K2,Sheet1!$M:$M,FALSE),1),MATCH("NOTEORDER",OFFSET(Sheet1!$A$1,MATCH($K2,Sheet1!$M:$M,FALSE)-1,0,MATCH($K3,Sheet1!$M:$M,FALSE)-MATCH($K2,Sheet1!$M:$M,FALSE),1),FALSE)),"")

In M2, enter the formula

=IFERROR(INDEX(OFFSET(Sheet1!$A$1,MATCH($K2,Sheet1!$M:$M,FALSE)-1,4,MATCH($K3,Sheet1!$M:$M,FALSE)-MATCH($K2,Sheet1!$M:$M,FALSE),1),MATCH("NOTEORDER",OFFSET(Sheet1!$A$1,MATCH($K2,Sheet1!$M:$M,FALSE)-1,0,MATCH($K3,Sheet1!$M:$M,FALSE)-MATCH($K2,Sheet1!$M:$M,FALSE),1),FALSE)),"")

In N2, enter the formula

=IFERROR(INDEX(OFFSET(Sheet1!$A$1,MATCH($K2,Sheet1!$M:$M,FALSE)-1,5,MATCH($K3,Sheet1!$M:$M,FALSE)-MATCH($K2,Sheet1!$M:$M,FALSE),1),MATCH("NOTEORDER",OFFSET(Sheet1!$A$1,MATCH($K2,Sheet1!$M:$M,FALSE)-1,0,MATCH($K3,Sheet1!$M:$M,FALSE)-MATCH($K2,Sheet1!$M:$M,FALSE),1),FALSE)),"")

and copy down to match your list of student IDs. Note that if column E or F are blank when column A is NOTEORDER then the return will be a 0 and not a blank. If you want to hide the 0s, then use the custom format 0;-0;;@ on the cells in M and N