Looking up row numbers from a cross-reference chart

Hi Team,

I consider myself fairly good with excel but this has me stumped. I currently have a spreadsheet with each NBA team in Row 1 and the teams they played, in order, below them. For each team, I need to know what numerical game that was for the opposing team via their row number.

For example, you can see in the "F" column that those are the teams that Cleveland played in order. Cleveland's first game was against the NY Knicks; however, if you look in the "T" column for the NY Knicks' games, that game against Cleveland was their second game. So for all of each team's opponents, I need to know what game that was for that opponent which can be deduced from the row number.

I'm currently doing it very manually. For example, if I'm doing Atlanta I replace all "atl" and "@ atl" with "=ROW()" and then conditionally format all numbers to highlight so I can easily see them. Then visually reference each Atlanta game with it's opponents highlighted row number. It's a pain and there has to be a formula somewhere to make it easier.

Any help would be appreciated.

Sorry about that; I was trying to fit all the teams without scrolling left or right. I can't show you because the thread won't let me attach any more spreadsheets. But let's say I'm determining Atlanta's opponents. I just Replace all "atl"s with =ROW() and it'll tell me which row the atl's are in across the spreadsheet. Then I do down Atlanta's opponents one-by-one and cross reference them visually. I insert a column next to Atlanta and type them in manually.

I created a sports betting spreadsheet and obtaining this info is imperative to update the data correctly and determine a win rate above 52.4% (for all you sports bettors out there that is the break-even point for sports betting).

What I have looks like the attachment. I'm doing it for Golden State (gsw). I inserted a column (K) and am typing the row number of the next team Golden State played. When it's done I'll copy and paste all of those row numbers into my other spreadsheet where it will auto-update.

This formula was really close. I received a few #N/A that I can't figure out. Also, oftentimes teams play each other more than once, but the formula does not account for that. For example, GSW played the Lakers (lal) in their second game and also in their tenth game, but the formula stays with the first game, so it shows both games being the fifth for the Lakers.

For some reason all of the spurs (sas), knicks (nyk), and pelicans (nor) games are coming up as #N/A.

Yep...should've noticed that in your formula. I changed the headings and the N/As are gone. Now if you could find a way to take the next iteration of the game played with the same team all will be well in the world. Well, my world at least