Need Help Creating INDEX/MATCH Formula to Return Values from Data Table

Hello, I am requesting help with creating a table from a database containing data of golf scores that need to be returned in a report by date, by player, by hole. I have attached an example below that provides the sample database and sample expected results report.

The sample database contains golf scores (hole by hole) for players playing on certain dates. I need to create a report that look up the name for each player, by week, and returns each player’s score for each hole played. As each player signs up for a weekly game they may be assigned to a different tee time (hence the reason why the database does not maintain player scores in a consistent order). Therefore a player’s score could be contained in the database at a different location from week to week, however, each record for a given player will always have their individual hole scores listed in columns immediately to the right of their name.

I’ve attempted to develop an INDEX formula using the MATCH function to find the correct row and column in the data table based on respective date and player name criteria. What I haven’t been able to tackle is how to get the formula to return hole scores listed to the right of each player. Perhaps including the OFFSET function into the formula may be the key, or perhaps the formula needs to be an array formula (although I don’t require any calculations).

Additionally, the formula that is developed needs to be able to react to a dynamic database meaning that new player records will be added on a weekly basis so the lookup array needs to be able to expand as the number of data records increase.

The formula should be able to search for the date of the golf round and for each respective player’s name in the database and then return the date, name and each of the player’s 18 hole scores as shown in the “Expected Results” table in the attached sample. Any help or guidance that could be provided would be appreciated.

I hadn't considered using a macro hoping that a formula would be more efficient. Perhaps I should have provided a bit more detail regarding the database and how it is populated. Not sure if this would help or not. The database, located in it's own worksheet, is a record of a weekly golf game and contains data elements the are written from another worksheet within my workbook in an order in which a player is assigned to a tee time and two-man team. Unless I am wrong, sorting the database would ruin the integrity of the data as it was written to the database. I dont disagree with you that my problem may appear to be easily accomplished with a data sort. However, the report I am attempting to prepare needs to be prepared on yet a third worksheet and used to drive other needs and calcuations in my workbook.

Does that make any more sense?

Sorry that we appear to not be communicating very well. I made mention of multiple workbooks but did mention multuple worksheets in a single workbook so I'm not sure I understand where you are coming from. I can't state it more simply, but I cannot sort the database. Doing so would render other worksheets in the workbook useless as the data upon which those worksheets rely, would be out of position. I may very well be off track in my request, but it seems feasible to me that I can achieve what I am after using a formula and that does not include sorting the database. Any alternate direction you, or someone else could provide would be much appreciated.

Thank you sktneer. I will give this a try and let you know the results.

Sktneer, Again thank you for the solution provided. After giving it a thorough review and testing, the solution works perfectly and has saved me a lot of time.