I feel like I'm pretty close with this formula, but I'm just missing one little piece. I have a bunch of soccer players listed on rosters and I want to verify if they played last year, then output which team they played on previously, as well as the team they're currently playing on. The problem I'm running into is that I can verify whether the player existed the previous season or not, but I can't get Sheets to return the row number that player was found in, in order to output the team they played on properly. This formula just output the current row instead of the row the player was found on the 2017Rosters sheet, which results in the same value usually because the teams are listed in the same order on the other sheet.
=IF( COUNTA( IFERROR( FILTER( '2017Rosters'!B:B , '2017Rosters'!B:B=B2 ) ) ) , C2&"|"&'2017Rosters'!D:D, C2 )