Matching a Row from Table 1 to a Row in Table 2 (non-numeric), and as a result show a cell

I have two tables where i am trying to match a row from table 1 to table 2. If the row matches, i want to display a specific cell in table 2 in a seperate column not affiliated to the previous 2 tables.

Real world/actual application/situation:
I have a race team of 20 people and a total of 50 races.
Each person has a tab in the excel workbook and they are to put an "x" in the corresponding cell of a table that shows yes they are going (indicated by an "X" in the cell) next to a list of avaialbe races they can participating in.

I have a "team" tab that is trying to tally the "person" tabs in a cell. (if the person puts an "x" in the cell on thier individual tab, indicating they are going to participate in that race thier name (which is the tab name) will appear on the "team" tab, also - if they are not going to participate it leaves thier name off/blank of the desired outcome/cell, so if no one is going to that race, the cell is blank, if 3 people are going it ONLY shows the three people that are going - preferable with a comma and a space between the names except for the last one...i.e. "Dan, Joe, Tom")

This is to make it so i can see the team tab, and be able to see all who are going to participate in any give race without having to look through each individual tab.

Table 1 = Actual inputs
Table 2 = all possible outcomes

I'm sure there is a better way to do this, but this is what i have now and i can't seem to get the result i want...please help!

also, if the outcomes can add a comma and a space to the names if another name comes after it to make it easier to read that would be awesome. I tried nesting but i had 91 possible outcomes and it only allows up to 64.

ALSO-this may help
Table 1
I have accross the top of the tables the 20 person names
I have on the first column the 50 names of the races
there is a corresponding "x" under the name in the row of the race they are participating in

Table 2
Same conficguration top - 20 person names
First column - 50 race names
-All possible outcomes-
Additional column on the end of the table with the names of each person that has an "x" in that row

Can you upload the workbook or a simplified version of the workbook (i.e with only 3 runners) (Go Advanced> Manage Attachments)?

Okay, in Table 1, C3

=IF(INDIRECT("'"&C$2&"'!J"&ROW(A3))="","", "x")

If there aren't going to be spaces in the names (i.e. like you have it on the example) then this can be simplified to

=IF(INDIRECT(C$2&"!J"&ROW(A3))="","", "x") as you won't need the single quotes around the name.

I'm not sure of the purpose of Table 2. Is it just to be able to be able to concatenate the names for the "registered" list? What are the columns "Order 1" and "Order 2" for?

Thank you ChemistB. I put the formula in table 1 that you transcribed above.

=IF(INDIRECT("'"&C$2&"'!J"&ROW(A3))="","", "x")

I then dragged it through the table and it filled in the selections as it should.

But how do i get in the first tab starting in J3 the names to show up like this-

(all three have x's in table 1) Dan, Dirk, Andy
-or-
(if there is only an x in Dan and Andy in table 1) Dan, Andy

I added the excel example with your changes and highlighted the areas i am trying get an out come in yellow...see the first and last tabs


Table 2 may not be needed as you said. I was tring to give a table with all possible outcome to match against and give a result to display....maybe wasted my time
Order 1 and Order 2 were just ordering columns wher ei was trying to filter the table...those can be ignored

Okay, in order to get your concatenated list, we're going to introduce a UDF (user defined function) which requires VBA. That means your file type needs to be saved a xlsm instead of xlsx.

The following code goes into a module in the workbook. First open up the VBA editor (ALT F11) and "Insert> Module" Then copy and paste the code and close the vba editor.

Please Login or Register  to view this content.

For your purposes, the UDF will be of the form
=CAT(range, "delimiter")
where range is your expected range you want to concatenate and
delimiter is what you want to separate each entry with. " " would be a space, "," would be a comma, ", " would be a comma followed by a space (my personal favorite)

Next, we delete your table 2 and in H3 enter an arrayed formula
=IFERROR(INDEX($C$2:$E$2,SMALL(IF($C3:$E3="x",COLUMN($A$1:$C$1),10000000),COLUMN(A1))),"")
To make a formula arrayed, you need to enter it with CNTRL SHFT ENTER instead of a simple ENTER. If done properly, you'll see brackets {} appear around the formula.
You drag that formula across and down.
That should give you a consecutive list of the names in each race.

Then in G3 copied down
=CAT(H3:Z3, ", ")
You can reference Column G in your TEAM spreadsheet to get the roster for each race. See attachment.
Questions?

You didn't expand your ranges
In X3
=CAT(Y3:AR3,", ")

in Y3 (as an arrray)
=IFERROR(INDEX($C$2:$V$2,SMALL(IF($C3:$V3="x",COLUMN($C$1:$V$1)-2,10000000),COLUMN(A1))),"")
Your table 1 ranges from C to V
This part COLUMN($C$1:$V$1)-2
is minus 2 because you want values to go from 1 to 20, not 3 (col C) to 22 (col V)
Hope that helps