If cell contains partial text return value from other cell

[9191w]

Hey!

I have 3 cells, 2 are with names, they are not sorted, as they are extracted from two different webpages.

Basically eg.
Cell 1 (full namem, random order): Masahiko Inoha
Cell 2 (partial name, usually the following): M. Inoha (in some cases however full names)
Cell 3 (extracted web code, on the same row as cell 2): 3445

Now I want the value of Cell 3 to align with Cell 1 through a partial match with cell 2.

I used: =IF(ISNUMBER(SEARCH(C:C,K3)),N3), and tried VLOOKUP variations too but without success.

Thank your for your time!

 

[jim may]

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3			Masahiko Inoha	3445	<< Formula Extracts from Cell K3 the Right-Most Single Name Only						M. Inoha			3445
4					and searches for it In Cell D3 AND If Found returns the
5					Value in Cell N (Same row).

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D3 =IF(SEARCH(RIGHT(K3,LEN(K3)-FIND(" ",K3)),C3,1),N3,"") <tbody> </tbody>

<tbody>
</tbody>

 

[9191w]

Many thanks for your quick response!

Apologies for not explaining myself properly. Column K is not sorted in the same way as C is, it's all mixed up. How would I need to change the above to solve that? Didn't manage myself.

Best

 

[9191w]

Just to add, my aim is to sort it by column C, so that I have the fitting number codes per name that fit to C.

 

[jim may]

Supply a Table of Data Sufficient in size to prepare and solve the problem, please.

 

[9191w]

C	D	E	F	G	H	I	J	K	L	M	N
Hiroki Mizumoto			hiroki-mizumoto	49348	hiroki-mizumoto/profil/spieler/49348			Patric	https://instatscout.com/en/players/48629	null	48629
Daiki Niwa			daiki-niwa	79706	daiki-niwa/profil/spieler/79706			T. Miyayoshi	https://instatscout.com/en/players/144081	null	144081

<tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Kazuyuki Morisaki			kazuyuki-morisaki	106069	kazuyuki-morisaki/profil/spieler/106069			T. Matsumoto	https://instatscout.com/en/players/486368	null	486368
Ryotaro Hironaga			ryotaro-hironaga	113621	ryotaro-hironaga/profil/spieler/113621			T. Aoyama	https://instatscout.com/en/players/30056	null	30056
Hirotsugu Nakabayashi			hirotsugu-nakabayashi	106272	hirotsugu-nakabayashi/profil/spieler/106272			T. Morishima	https://instatscout.com/en/players/283017	null	283017
Masato Kudo			masato-kudo	114202	masato-kudo/profil/spieler/114202			Y. Chajima	https://instatscout.com/en/players/222766	null	222766
Osamu Henry Iyoha			osamu-henry-iyoha	332628	osamu-henry-iyoha/profil/spieler/332628			K. Mukuhara	https://instatscout.com/en/players/35033	null	35033
Takumi Miyayoshi			takumi-miyayoshi	79742	takumi-miyayoshi/profil/spieler/79742			S. Inagaki	https://instatscout.com/en/players/222763	null	222763
Kazuhiko Chiba			kazuhiko-chiba	31065	kazuhiko-chiba/profil/spieler/31065			S. Takahashi	https://instatscout.com/en/players/232655	null	232655
Takuto Hayashi			takuto-hayashi	126383	takuto-hayashi/profil/spieler/126383			Y. Nogami	https://instatscout.com/en/players/173318	null	173318
Nathan Burns			nathan-burns	43087	nathan-burns/profil/spieler/43087			K. Chiba	https://instatscout.com/en/players/30008	null	30008

<tbody>
</tbody>

Hope the above works in format. Bit chaotic as I was scraping two webpages, list goes on for about 1000 rows.

Column C, K & N is of interest, ignore the rest. K (short name) + N (code) are matching in each row.

C is mixed and I wish to match them with the corresponding code per name in N. So that basically the code shows up in column E for each player corresponding in the same row in C.

Hope all makes sense, many thanks for your time!

 

[9191w]

Bump

 

[Aladin Akyurek]

Here are your C, N, and K records...

Row\Col	C​	K​	N​
5​	Hiroki Mizumoto	Patric	48629​
6​	Daiki Niwa	T. Miyayoshi	144081​
7​	Kazuyuki Morisaki	T. Matsumoto	486368​
8​	Ryotaro Hironaga	T. Aoyama	30056​
9​	Hirotsugu Nakabayashi	T. Morishima	283017​
10​	Masato Kudo	Y. Chajima	222766​
11​	Osamu Henry Iyoha	K. Mukuhara	35033​
12​	Takumi Miyayoshi	S. Inagaki	222763​
13​	Kazuhiko Chiba	S. Takahashi	232655​
14​	Takuto Hayashi	Y. Nogami	173318​
15​	Nathan Burns	K. Chiba	30008​

What is the output that you require?

 

[9191w]

Thank you for taking the time to put it in a better format!

Column K & N are fitting, eg. N5 is the actual code of the person named "Patric" (K5). In C the same name of lists (but full names instead) comes but in mixed order. I aim to have the code of column N match with each name in column C outputted in column E.

In the short sample above only C12 fits with K6/N6, C13 fits with K15/N15 - the others would appear more down the columns.

Best

 

[Aladin Akyurek]

Thank you for taking the time to put it in a better format!

Column K & N are fitting, eg. N5 is the actual code of the person named "Patric" (K5). In C the same name of lists (but full names instead) comes but in mixed order. I aim to have the code of column N match with each name in column C outputted in column E.

In the short sample above only C12 fits with K6/N6, C13 fits with K15/N15 - the others would appear more down the columns.

Best

In E5 enter and copy down:

=IFERROR(VLOOKUP("*"&IF(ISNUMBER(FIND(".",K5)),REPLACE(K5,1,FIND(".",K5),""),K5)&"*",CHOOSE({1,2},$C$5:$C$15,$N$5:$N$15),2,0),"NA")