Search code examples
excelexcel-formulaexcel-tables

Pulling Data Between Tables Using =IF Formula

I am trying to create a Fantasy Football Cheat sheet since my league drafts offline. The excel file has two sheets, one with all of the available players to draft sorted in a table, the other with my team as I draft them sorted into a table. I am trying to make it so I can mark a player with a designated letter and that will trigger the players information to be pulled into my team table.

What I have figured out is that I am able to pull the information into the other table every time I place an L (for Logan) in the drafted column using this formula:

=IF(Table2[[#All],[Drafted]]="L",Table2[Player Name], "Empty")

The issue I am having is the IF function pulls the player data, but it pulls it into the same cell in sheet 2 that the player is located in on sheet 1. I need to get it so the table looks for the next L and pulls in the player data into the next empty spot on the team table. Refer to the image below:

https://madshot.net/2af3822b36b1.png

I appreciate any suggestions to help me get past this road block.

Solution

  • You can use this formula entered at the first cell for column Player: 

    =IFERROR(INDEX(Table2[Player Name],
   AGGREGATE(15,6, Table2[ADP]/(Table2[Drafted]="L"),ROW(1:1))),"")

    You can use VLookup for the other columns once the players' names were imported. Alternatively you can use the same formula just by substituting Table2[Player Name] with the appropriate column, i.e. Table2[Position].

    p.s. I used Table2[ADP] to benefit from the existence of the row numbers in the table, instead of the otherwise more complex ROW(Table1[Drafted])-ROW(Table1[#Headers]).