CODEWITHSUNDEEP
excelexcel-formulaformulasvba
Michelle
Michelle

Reputation: 13

excel lookup value on another sheet and return value of another column from that sheet

I have a workbook it has a main sheet called Players that has Ticket#, Table#, Name (then other information).

Example:

Players

I also have sheets with table assignments.

Example:

Tables

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

Answers (2)

user4039065
user4039065

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

OES
OES

Reputation: 321

I wouldn't suggest such approach but since you already set it then:

  1. 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).

  2. 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)

  1. 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

Related Questions