Advanced Lookup formula

[Halljrod]

[TABLE="width: 531, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Round[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Player Available[/TD]
[TD]QB[/TD]
[TD]Aaron Rodgers[/TD]
[TD]Drew Brees[/TD]
[TD]Peyton Manning[/TD]
[TD]Cam Newton[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RB[/TD]
[TD]Adrian Peterson[/TD]
[TD]Arian Foster[/TD]
[TD]Doug Martin[/TD]
[TD]Marshawn Lynch[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]WR[/TD]
[TD]Calvin Johnson[/TD]
[TD]A.J. Green[/TD]
[TD]Brandon Marshall[/TD]
[TD]Demaryius Thomas[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TE[/TD]
[TD]Jimmy Graham[/TD]
[TD]Rob Gronkowski[/TD]
[TD]Jason Witten[/TD]
[TD]Tony Gonzalez[/TD]
[/TR]
</tbody>[/TABLE]



I’m having trouble with the look up formula for the table above. What I need this table to do is act as a heads up display that will update and show the best player available, second best players available, etc… as well as my selections based on draft round. This table is populated automatically by 4 cheat sheets like the one below, which are constantly being updated to reflect who has been picked in the draft thus far.

[TABLE="width: 330"]
<tbody>[TR]
[TD]ADP[/TD]
[TD]UD[/TD]
[TD]QB[/TD]
[TD]FPts[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]Drew Brees[/TD]
[TD]422.268[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x[/TD]
[TD]<strike>Tom Brady</strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD]Peyton Manning[/TD]
[TD]395.77[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]Aaron Rodgers[/TD]
[TD]427.62[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD][/TD]
[TD]Matt Ryan[/TD]
[TD]365.226[/TD]
[/TR]
</tbody>[/TABLE]

I think a hypothetical situation is a best way to explain the mechanics of my sheet. I’m second to pick overall and Peyton Manning was the first pick in the draft. Lets say I decide to draft Adrian Peterson in the first round. In order for my spreadsheet to acknowledge the fact that I have picked Adrian, I would need to manually delete the formulas in the “QB”, “WR”, and “TE” first round cells.

[TABLE="width: 447"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]QB[/TD]
[TD][/TD]
[TD]Drew Brees[/TD]
[TD]Cam Newton[/TD]
[TD]Tom Brady[/TD]
[/TR]
[TR]
[TD]RB[/TD]
[TD]Adrian Peterson[/TD]
[TD]Arian Foster[/TD]
[TD]Doug Martin[/TD]
[TD]Marshawn Lynch[/TD]
[/TR]
[TR]
[TD]WR[/TD]
[TD][/TD]
[TD]A.J. Green[/TD]
[TD]Brandon Marshall[/TD]
[TD]Demaryius Thomas[/TD]
[/TR]
[TR]
[TD]TE[/TD]
[TD][/TD]
[TD]Rob Gronkowski[/TD]
[TD]Jason Witten[/TD]
[TD]Tony Gonzalez[/TD]
[/TR]
</tbody>[/TABLE]

The image above reflects my dilemmas. (1) Deleting each cell takes time and leaves room for errors. (2) Once the “best” player is deleted, the table is not dynamic enough to pivot and shift all remaining players down a round. The formula for the look up is:

=VLOOKUP(SMALL($G$35:$G$54,COLUMNS($D4:D4)),$G$35:$J$54,3,0)

For right now, the tables are being populated based on the average draft position which is derived from Fantasy Football mock draft data. Going forward, I would like to populate the table based on my own analysis of past performance and future projections. The cheat sheet table would descend in order of decreasing player value. So if I were drafting quarterbacks from the cheat sheet above Drew Brees should appear in round 1 followed by Tom Brady in round 2 etc…

Any help will be greatly appreciated!