Challenging Problem - IndexMatch Formula with Dynamic Range

Ok I have a pretty complicated question. I have attached a workbook as an example.

So I have the teams listed in out in cells N1:N30. What I would want is an IndexMatch formula that looks up one of those team names and then matches it to column A. Once it finds the team name in column A, then I would need the formula to use the OFFSET function or something similar to dynamically go down the column to the "Gametime Forecast" cell. The trick with this is that this "Gametime Forecast" cell will dynamically change. So it could be 5 rows down from the matched team name in column A, or it could be 15 rows down. Once the formula reaches this "Gametime Forecast" cell, then it needs to move 9 columns to the right, and pick up the value in column J.

So the end result should be if it looks up "Marlins" it returns cell J53. If it looks up "Nationals" it returns cell J53. If it looks up "Diamondbacks" it returns cell J81. If it looks up "Rockies" it returns cell J81.

Its a pretty complicated problem. Let me know if you have any questions or a better way to pick up that cell reference.

Thanks!
Book1.xlsx

Originally Posted by benishiryo

hi there. those egs you mentioned all had 0%, so i tried for Giants. does this work for you?

Formula:

Please Login or Register  to view this content.

this might work faster:

Formula:

Please Login or Register  to view this content.

It works marvelously! Very impressed! Thank you so much!