Lots of data, need help organizing into usable format

New Contributor

I have an insurmountable amount of data that I’m trying to move into a more user-friendly format to then run statistical analyses. I am using Microsoft 365, Excel Version 2102 64-bit on a Windows 10.


I'm looking for a formula to put into each cell across the top row of a new sheet that I can then copy and repeat down the lines because I have other items that I'm going to pull from the original data. If someone can provide me a type of 'template' that I can then swap out the pieces so that I can continue to apply to the other data values that I'm trying to pull into this sheet.  I'm hoping to pick out pieces of information to include as I have more than I actually need.  For example, I will look for Student 1 Fall 2020 Primary Major, but I don't necessarily want all the information that I have for each person's entries to be automatically displayed as an array.


Do I need some type of nested Xlookup? Do I need a helper column?  I am a total novice and in over my head.  Any help will be greatly appreciated as the only way I know how to do this is by hand which will take me hours and hours, weeks and weeks.


I'm looking for a formula that will search Student Identifier (from column A of the original data), then the term (from column B of the original data), then the Data Element from column C, and give me the data value from column D and put it into this spreadsheet.

I've included a file to help show what I'm working with.  The sheet has two tabs:  Tab 1 "Data as is" is a snippet of original data. Tab 2 "data format_end goal" which is what I'm hoping to be my transformed data to be more user friendly for statistical analysis in SPSS.

I thank you all in advance for their time and efforts.  I'm in awe of the Excel Gurus collective knowledge and hope someone can share some of that knowledge with me.

4 Replies

@DMcCauley5082 When you mention "an insurmountable amount of data" that you want distribute over a number of columns with texts and number is crying out for Power Query. Are you familiar with that?


Am not able to send you a demo right now, but I'm sure that someone else will.

@Riny_van_Eekelen I, unfortunately, am not familiar but will go some Google searching and watch a few videos to see what I can learn about it.  Thanks for pointing me in a starting direction!