Combine Data and Sort

I have two images that I hope will help explain what I would like to accomplish. Please let me know if more information is needed. Any help would be greatly appreciated.

spreadsheet_01.PNG

Column A is static data that is preset in the spreadsheet. Column B and C data will be collected from students through the use of a Google Form, so the student number will be randomly placed in the column and not all student's may fill out the form. Column C represents a class section that they make. I am currently using the formula:

Formula:

Please Login or Register  to view this content.

to combine the values in column B and C. However, I need the student number values in column D to line up with the student number values in column A. For example, student choice "1064 Basket Weaving" in column D is in row 11 to match up with the static data in column A.

I may be making this harder than needed, but to accomplish my task I thought about making a new sorted column like the image below:

spreadsheet_02.PNG

In column E, the plan is to use the formula:

Formula:

Please Login or Register  to view this content.

The formula aligns the values with column A if I only have the four digit number in column D, but my hang up is that I need the formula to only look for a match in the first four digits of column D. How can I modify the formula to do that? If there is an easier way to perform this task I am open to ideas. Thank you.

Sorry about that. Here is the spreadsheet to give it a go. Thanks.

That works! Thank you!

I got Pauleyb's formula to work...thanks for the help. I appreciate it!

Request Online Test Studnet Number End.xlsxOne small change. How would I use the formula if the student number was at the end instead of the beginning? I need to be able to group the students by their selection. Thank you.

Yeah, I found that I can get the results I was looking for with the filter options. Thanks for all the help. I appreciate it.