I created a street index like your issue. Old school AML programs back in the day (2000). A map like road atlas with the street index to the right. This still can be done in GIS and Python. I would recommend working through this issue one piece at a time. You can use excel but since you are working in GIS it would be better if you create a file geodatabase FGDB with all the data. I would use only python for the whole process but a combination of ArcMap and Python will work.
First, create a table with unique street names.
Second, add columns as needed. This table will capture all the iterations.
Example: street prefix, street name, street type, street suffix, street name full ( N Main St, Main St N...), Grids - g1, g2, g3, etc. (I would add 10 grid numbers), counter to capture how many grids added to that unique street name.
Third, use the street against the grid FC, what you do is clip and then iterate through each of those clipped streets and add that grid number to the table with a select statement.
So, its clip streets from grid, iterate thorough each street in that subset, select unique street name in table, calculate values. You would use the counter (stcntr += 1) column to calc the correct grid column.
Example: N Main St, A, B, ,,,,,,,,2 and all the other columns you added.
N Main St, Grid A, Grid B, with 2 iterations
Fourth, after all the iterations you will go through the unique street name table to create another table.
This would be the final table and the hardest to configure because of the specific code.
Example, N Main St GA-GE, GW, GX
Example, GA-GE (GA,GB,GC,GD,GE Then GW and GX).
You would need to write code to sort or capture A,B,C…
Once you have it set up the final process is table (raw data) to final table.
The next step is to add the index to the map. I used word two column configuration and copy and paste. It is a manual process to keep it updated and I added new streets monthly or as needed.