I am building a track and field event lineup and need to search for an athletes name in an array of cells and then return that value to a cell next to the athlete's name. I have a data validation for all of the cells under each specific event and when I select an athlete for an event I want that event name to populate next to that athlete's name under the Events By Athlete table.

For example, If I were to select Athlete A for leg number 2 of of the 4x100, I would want 4x100 to populate next to populate in cell B5.

I have tried LOOKUP functions, excelformulabot, and match functions but have come up empty.

CodePudding user response：

1. Remove all merged cells from the sheet (recommended but not 100% necessary in your case)
2. Where you need the "beauty" of the merged cells, use "center across selection"
3. Make a new column for every event for example on the left and write for each player the name of the event (write 4 times 4x400m). This will make it much easier later. 4)These columns you can hide later on, when everything works
4. If you have office 365, then use VSTACK function to put all the names of all small tables on top of each other, same with the event names in the new column
5. If you don't have VSTACK you can do referencing manually into a new column somewhere in you workbook.
6. Use the Index and Filter function so search for the event in the VSTACK. First the VSTACK list gets filtered for the Athlete, then with the Index function the Event Number gets picket out. For that it is important that the events take place exact in that order as you put it in the VSTACK.