If cell contains partial text return value from other cell

9

9191w

New Member
Joined
Aug 31, 2017
Messages
13
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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Excel 2010
ABCDEFGHIJKLMN
Masahiko Inoha<< Formula Extracts from Cell K3 the Right-Most Single Name OnlyM. Inoha
and searches for it In Cell D3 AND If Found returns the
Value in Cell N (Same row).

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3445[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3445[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=IF(SEARCH(RIGHT(K3,LEN(K3)-FIND(" ",K3)),C3,1),N3,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
[TABLE="width: 0"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hiroki Mizumoto[/TD]
[TD][/TD]
[TD][/TD]
[TD]hiroki-mizumoto[/TD]
[TD]49348[/TD]
[TD]hiroki-mizumoto/profil/spieler/49348
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Patric[/TD]
[TD]https://instatscout.com/en/players/48629[/TD]
[TD]null[/TD]
[TD]48629[/TD]
[/TR]
[TR]
[TD]Daiki Niwa[/TD]
[TD][/TD]
[TD][/TD]
[TD]daiki-niwa[/TD]
[TD]79706[/TD]
[TD]daiki-niwa/profil/spieler/79706
[/TD]
[TD][/TD]
[TD][/TD]
[TD]T. Miyayoshi[/TD]
[TD]https://instatscout.com/en/players/144081[/TD]
[TD]null[/TD]
[TD]144081[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]Kazuyuki Morisaki[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD]kazuyuki-morisaki[/TD]
[TD]106069[/TD]
[TD]kazuyuki-morisaki/profil/spieler/106069
[/TD]
[TD][/TD]
[TD][/TD]
[TD]T. Matsumoto[/TD]
[TD]https://instatscout.com/en/players/486368[/TD]
[TD]null[/TD]
[TD]486368[/TD]
[/TR]
[TR]
[TD]Ryotaro Hironaga[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD]ryotaro-hironaga[/TD]
[TD]113621[/TD]
[TD]ryotaro-hironaga/profil/spieler/113621
[/TD]
[TD][/TD]
[TD][/TD]
[TD]T. Aoyama[/TD]
[TD]https://instatscout.com/en/players/30056[/TD]
[TD]null[/TD]
[TD]30056[/TD]
[/TR]
[TR]
[TD]Hirotsugu Nakabayashi
[/TD]
[TD][/TD]
[TD][/TD]
[TD]hirotsugu-nakabayashi[/TD]
[TD]106272[/TD]
[TD]hirotsugu-nakabayashi/profil/spieler/106272
[/TD]
[TD][/TD]
[TD][/TD]
[TD]T. Morishima[/TD]
[TD]https://instatscout.com/en/players/283017[/TD]
[TD]null[/TD]
[TD]283017[/TD]
[/TR]
[TR]
[TD]Masato Kudo[/TD]
[TD][/TD]
[TD][/TD]
[TD]masato-kudo[/TD]
[TD]114202[/TD]
[TD]masato-kudo/profil/spieler/114202
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y. Chajima[/TD]
[TD]https://instatscout.com/en/players/222766[/TD]
[TD]null[/TD]
[TD]222766[/TD]
[/TR]
[TR]
[TD]Osamu Henry Iyoha
[/TD]
[TD][/TD]
[TD][/TD]
[TD]osamu-henry-iyoha[/TD]
[TD]332628[/TD]
[TD]osamu-henry-iyoha/profil/spieler/332628
[/TD]
[TD][/TD]
[TD][/TD]
[TD]K. Mukuhara[/TD]
[TD]https://instatscout.com/en/players/35033[/TD]
[TD]null[/TD]
[TD]35033[/TD]
[/TR]
[TR]
[TD]Takumi Miyayoshi
[/TD]
[TD][/TD]
[TD][/TD]
[TD]takumi-miyayoshi[/TD]
[TD]79742[/TD]
[TD]takumi-miyayoshi/profil/spieler/79742
[/TD]
[TD][/TD]
[TD][/TD]
[TD]S. Inagaki[/TD]
[TD]https://instatscout.com/en/players/222763[/TD]
[TD]null[/TD]
[TD]222763[/TD]
[/TR]
[TR]
[TD]Kazuhiko Chiba[/TD]
[TD][/TD]
[TD][/TD]
[TD]kazuhiko-chiba[/TD]
[TD]31065[/TD]
[TD]kazuhiko-chiba/profil/spieler/31065
[/TD]
[TD][/TD]
[TD][/TD]
[TD]S. Takahashi[/TD]
[TD]https://instatscout.com/en/players/232655[/TD]
[TD]null[/TD]
[TD]232655[/TD]
[/TR]
[TR]
[TD]Takuto Hayashi[/TD]
[TD][/TD]
[TD][/TD]
[TD]takuto-hayashi[/TD]
[TD]126383[/TD]
[TD]takuto-hayashi/profil/spieler/126383
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y. Nogami[/TD]
[TD]https://instatscout.com/en/players/173318[/TD]
[TD]null[/TD]
[TD]173318[/TD]
[/TR]
[TR]
[TD]Nathan Burns[/TD]
[TD][/TD]
[TD][/TD]
[TD]nathan-burns[/TD]
[TD]43087[/TD]
[TD]nathan-burns/profil/spieler/43087
[/TD]
[TD][/TD]
[TD][/TD]
[TD]K. Chiba[/TD]
[TD]https://instatscout.com/en/players/30008[/TD]
[TD]null[/TD]
[TD]30008[/TD]
[/TR]
</tbody>[/TABLE]

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!
 
Last edited by a moderator:
Upvote 0
Here are your C, N, and K records...

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

What is the output that you require?
 
Upvote 0
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
 
Upvote 0
9191w said:
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
Click to expand...

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")
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Index/Match - Return value if partial text from cell contained anywhere in separate column
Replies
5
Views
4K
excelnewb221
E
M
  • Solved
VBA If text is found in range, replace with text from another cell, based on partial match with adjacent cell
Replies
7
Views
3K
MikeMB
M
L
  • Question Question
Return text from cell in column containing text, as long as values in other columns match several criteria
Replies
7
Views
904
etaf
etaf
E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
4K
Kevin_Li
Kevin_Li
B
  • Solved
Header Return with PARTIAL Text Match
Replies
3
Views
606
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top