Formula for Filling Data based on Criteria from another cell

I have an attached workbook that requires some sort of match/index formula I feel. I'm just learning these so I'm not very good at them yet and haven't been able to figure one out for this particular problem.

In my worksheet I would like names to populate on sheet 2 based on the criteria entered in sheet 1 with that same matching name. For example, cell G4 of Sheet 2 should read the name 'Mike Trout' as he is an OF and was drafted by a team. I want Sheet 2 to fill with a name as soon as any kind of data is entered in column I of Sheet 1 for that name.

Any help on this would be greatly appreciated, thanks!

Try this formula in C4 of Sheet 2; fill down and across to H17.

Formula:

Please Login or Register  to view this content.

The work book is attached.

Originally Posted by FlameRetired

Try this formula in C4 of Sheet 2; fill down and across to H17.

Formula:

Please Login or Register  to view this content.

The work book is attached.

Hey this is actually the exact opposite of what I'm looking for, it should read Mike Trout in Cell G4 not Team 1 and I don't need the names in column A, just under the position the player qualifies for. Any ideas?

Originally Posted by nflsales

B4=IFERROR(INDEX('Sheet 1'!$C:$C,SMALL(INDEX(('Sheet 1'!$H$3:$H$16<>B$3)*10^10+ROW('Sheet 1'!$H$3:$H$16),0),ROW($A1))),"")

Try this and copy across

This is sort of what I'm looking for, although, I only want the sheet 2 to populate a player if data is entered in Column I of Sheet 1. So only Mike Trout should show up on Sheet 2 because he's the only player with data in column I of Sheet 1.

Originally Posted by sktneer

Try this Array Formula in B4 which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter and then copy across and down.

Perfect thanks! Thanks everyone for your help on this!