if partial data exists in one colum, copy to another column

Hi, new here and seeking assistance with copy data from one column to another.

Basically, 3 columns would work.

Column A: IF NAME IS IN "B" MATCH IT TO THE LIST IN COLUMN C AND PLACE THAT NAME HERE

Column B: POSSIBLY CONTAINS A NAME

Column C: CONTAINS A FULL LIST OF NAMES


So, what do I need to do with Column A to display the name from Column C if it appears in the Column B (examples below)

Column A (will remains blank unless a name in list C is in column B)


Column B (club members write their names and scores here):
JOHN STEVENS 41
PETER C 36
THOMAS G 21
STEVEN G 24
PETER J 3
PETER MACLOUD 21
STEVEN
THOMAS
THOMAS G 21
STEVEN G 24
JOHN STEVENS 12
PETER C could be attending
THOMAS G Did not attend


Column C has a set list of names as per our club member records:
JOHN STEVENS
PETER C
THOMAS G
STEVEN G
PETER J
PETER MACLOUD
STEVEN
THOMAS

Originally Posted by NBVC

It would be recommended to have a last name initial for everyone, because names like THOMAS and THOMAS G would be a problem if they are different people.

If you can do that, then you can follow these steps.

Add a helper column formula in D2:

=IF(ISNUMBER(MATCH(TRIM(C2)&"*",$B$2:$B$13,0)),COUNT(D$1:D1)+1,"")

copied down

In E2:

=MAX(D:D) to give total matches.

then in A2:

=IF(ROWS($A$1:$A1)>$E$2,"",INDEX($C$2:$C$9,MATCH(ROWS($A$1:$A1),$D$2:$D$9)))

copied down.

In the sample I removed a row from column B to show what the helper column and column A look like if a name is missing.

Hi there and thank you for your reply. I am new to spreadsheets and finding some difficulty.


I tried your example above and had an issue with it placing the incorrect name from my set list of names in column "C" (that only I can alter).


I am currently doing this all manually and it is taking up so much of my time, so I shall try to clarify my original question a bit better now.

The sheet is available for team managers to input the players name and score into column "B" (I have just notified them they need to put the players full name) and this is the only column they can enter data to as I have managed to lock the rest of the sheet.

Basically, I want to end up with 3 columns for the data.

Column "A" is going to be for my records only and will show the name which was input into column "B" by the coach or manager, and I need it to remain blank if there is no name in (which also matches the locked name in column C)

Column B: Usually contains the players name, followed by score as a number, and sometimes the players name is followed by other info such as "did not play" or "absent" but in that case I still need to have the players name show in column "A"

Column C: Is a full list of all the player's names which only I can enter (Currently 931 junior players but always increasing and decreasing)