Excel - Skip Blanks and re-display data in Office 365-CodePudding

I have a spreadsheet that displays players names if they haven't been previously chosen on another sheet.

This means I have two columns that look like this

      Column J    Column K
101   Position    Player
102   Blank       Blank
103   Defender    Blank
104   Defender    Blank
105   Defender    Player 1
106   Midfielder  Player 2
107   Midfielder  Blank
108   Midfielder  Blank
109   Forward     Player 3
110   Forward     Blank
111   Forward     Blank

I then have a separate table to display these Players

      Column N   Column O
101   Blank      Players
102   Sub 1      Blank
103   Sub 2      Blank
104   Sub 3      Blank

Column K will change depending on which players are chosen elsewhere.

There will always be 3 players in Column K, but in different positions.

I want to repeat the name of that player in Column O, to assign them to Sub 1, Sub 2 and Sub 3.

I have tried various IFERROR=... to no avail. It either returns nothing, or a 0. I am not sure if this is a limitation of the Online version of Office 365 Excel.

CodePudding user response：

Try this formula in cell O102:

=INDEX(K:K,AGGREGATE(15,6,ROW($K$102:$K$111)/($K$102:$K$111<>""),ROW(O102)-ROW($O$102) 1))

You can drag it down to the others cell.

Excel in office 365 will probably have a easier solution, but this one should work too.