Name extraction

[Obewan]

I have a formula which extracts the name in column C. I also need one which extracts the same name in column B but the answer must appear on the same row (see below) - the range for the formula I need need to cover rows 2:4500 for all columns as there is a lot of data. So I am assuming the formula would say if the name appears in column C then identify the same name on column A and and write the name in column B

Book1
	A	B	C
1	DATA	ANSWER new formula	DATA USING A FORMULA TO get name
2	Rug/Greyhound (box)
3	Rug 1BURN THE EDGE		BURN THE EDGE
4	Rug 2KICKING STONES		KICKING STONES
5	Rug 3ZAMBORA LOLA
6	Rug 4LIAR FOR HIRE		LIAR FOR HIRE
7	Rug 5VACANT BOX
8	Rug 6KINGSBRAE ANNA		KINGSBRAE ANNA
9	Rug 7ALL RUM		ALL RUM
10	Rug 8LEON KINGSBRAE		LEON KINGSBRAE
11	Tips:Rug 2Rug 4Rug 1Rug 6
12	Rug 1
13	BURN THE EDGEBK / D Jan-20 Zipping Garth x Specs Valley	BURN THE EDGE
14	Trainer: Desmond Winters (Rushforth)Owner: Robert Macmurray
15	Prizemoney: $24,940Best Win Times:GRA 19.95CAS 16.84CAS 23.50
Sheet1

 

[Peter_SSs]

I have a formula which extracts the name in column C.

Then why are some rows in column C blank?

Assuming blanks are possible in column C then try something like this

23 09 08.xlsm
	A	B	C
1	DATA	ANSWER new formula	DATA USING A FORMULA TO get name
2	Rug/Greyhound (box)
3	Rug 1BURN THE EDGE		BURN THE EDGE
4	Rug 2KICKING STONES		KICKING STONES
5	Rug 3ZAMBORA LOLA
6	Rug 4LIAR FOR HIRE		LIAR FOR HIRE
7	Rug 5VACANT BOX
8	Rug 6KINGSBRAE ANNA		KINGSBRAE ANNA
9	Rug 7ALL RUM		ALL RUM
10	Rug 8LEON KINGSBRAE		LEON KINGSBRAE
11	Tips:Rug 2Rug 4Rug 1Rug 6
12	Rug 1
13	BURN THE EDGEBK / D Jan-20 Zipping Garth x Specs Valley	BURN THE EDGE
14	Trainer: Desmond Winters (Rushforth)Owner: Robert Macmurray
15	Prizemoney: $24,940Best Win Times:GRA 19.95CAS 16.84CAS 23.50
Get Name

Cell Formulas
Range	Formula
B13:B15	B13	=FILTER(C$2:C$10,ISNUMBER(SEARCH(C$2:C$10,A13))*(C$2:C$10<>""),"")

 

[Obewan]

Then why are some rows in column C blank?

Assuming blanks are possible in column C then try something like this

23 09 08.xlsm
	A	B	C
1	DATA	ANSWER new formula	DATA USING A FORMULA TO get name
2	Rug/Greyhound (box)
3	Rug 1BURN THE EDGE		BURN THE EDGE
4	Rug 2KICKING STONES		KICKING STONES
5	Rug 3ZAMBORA LOLA
6	Rug 4LIAR FOR HIRE		LIAR FOR HIRE
7	Rug 5VACANT BOX
8	Rug 6KINGSBRAE ANNA		KINGSBRAE ANNA
9	Rug 7ALL RUM		ALL RUM
10	Rug 8LEON KINGSBRAE		LEON KINGSBRAE
11	Tips:Rug 2Rug 4Rug 1Rug 6
12	Rug 1
13	BURN THE EDGEBK / D Jan-20 Zipping Garth x Specs Valley	BURN THE EDGE
14	Trainer: Desmond Winters (Rushforth)Owner: Robert Macmurray
15	Prizemoney: $24,940Best Win Times:GRA 19.95CAS 16.84CAS 23.50
Get Name

Cell Formulas
Range	Formula
B13:B15	B13	=FILTER(C$2:C$10,ISNUMBER(SEARCH(C$2:C$10,A13))*(C$2:C$10<>""),"")

Genius!! Thank you!!

 

[Peter_SSs]

You are welcome.

But I am still wondering why some of those rows in column C do not have the name extracted since you have a formula to do that?

 

[Obewan]

The ones in columnn C are scratched and not running in the race. BTW I just noticed this formula stops working at row 1036 for some reason and I get SPILL? Any ideas. Here is the full formula with the one above in bold:
=IF(RIGHT(A1036,6)="Months","",IF(X1035="",IF(LEFT(A1037,3)="Tra",FILTER(AT$2:AT$4500,ISNUMBER(SEARCH(AT$2:AT$4500,A1036))*(AT$2:AT$4500<>""),""),""),X1035))

 

[Obewan]

Actually don't worry its a question for another thread as I have found the error but don't know the answer yet

 

[Peter_SSs]

I have not looked for or come across your other thread but a spill error means that the formula has found more than one value in AT2:AT4500 that can be found in A1036. That could happen, for example, if A1036 was "Rug 23CHATBOX" and column A contained, say, "CHATBOX" and "HAT"

If you have a solution from this thread and want to mark it, please mark the actual post that contains the answer. I have removed the mark from post #3 as clearly "Genius!! Thank you!!" is not the answer to your original thread question.

 

[Obewan]

I have not looked for or come across your other thread but a spill error means that the formula has found more than one value in AT2:AT4500 that can be found in A1036. That could happen, for example, if A1036 was "Rug 23CHATBOX" and column A contained, say, "CHATBOX" and "HAT"

If you have a solution from this thread and want to mark it, please mark the actual post that contains the answer. I have removed the mark from post #3 as clearly "Genius!! Thank you!!" is not the answer to your original thread question.

Yeah sometimes it can have two names of the same in the column - let me see what I can do to fix that. Thought that could be the issue but couln't find it yet.

 

[Peter_SSs]

If you want all matches listed & avoid the SPILL error try

=IF(RIGHT(A1036,6)="Months","",IF(X1035="",IF(LEFT(A1037,3)="Tra",TEXTJOIN(", ",1,FILTER(AT$2:AT$4500,ISNUMBER(SEARCH(AT$2:AT$4500,A1036))*(AT$2:AT$4500<>""),"")),""),X1035))