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 )

enter image description here

  • It looks that the screen is snip of the 2017Rosters. Is this right? What is the value of B2 an C2? By the way a screenshot is helpful to show what are you viewing but it doesn't help to reproduce the behaviour of your formula. Besides the screenshot add the sample data as text or even better create a demo spreadsheet, share it with anyone with the link to view only and add the link to the question. – Rubén Apr 25 '18 at 22:22
  • This is a screensnip of the main sheet, 2017Rosters is roughly the same data, though I think it has an extra rows, hence why it's row D I'm referencing in the output. – jbwharris Apr 26 '18 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.