Reputation: 13
I have a workbook it has a main sheet called Players that has Ticket#, Table#, Name (then other information).
Example:
I also have sheets with table assignments.
Example:
What I would like is for the sheet called table 1 to look through the Sheet Called Players for Table 1 and then return the Name of the person next to it.
Upvotes: 0
Views: 3499
Reputation:
Try this in 'Table 1'!B2,
=iferror(index(players!c:c, aggregate(15, 6, row(players!b$1:index(players!b:b, match(1e99, players!b:b)))/(players!b$1:index(players!b:b, match(1e99, players!b:b))=1), row(1:1))), text(,))
Fill down until you receive blanks.
Upvotes: 1
Reputation: 321
I wouldn't suggest such approach but since you already set it then:
Add one helper column in each Table to find ROW number in Players sheet (it's possible without it but not recomanded. You can hide it if you like).
Enter this formula in cell A2 of new cell:
=IFERROR(SMALL(IF(Players!$B$1:$B$100=1*TRIM(RIGHT(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),2)),ROW($B$1:$B$100)),ROWS(A$2:A2)),"")
(Confirmed with ctrl+shift+enter, not just enter)
Now set INDEX value based on that row from players sheets:
=IF($A2="","",INDEX(Players!C:C,$A2))
Extend formula to other cells and change columns as you like.
Upvotes: 0