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!

 

[9191w]

Thank you, however the above returns NA for me, any idea why?

 

[Aladin Akyurek]

Thank you, however the above returns NA for me, any idea why?

Seems ok over here. See the workbook which implements the suggestion: https://www.dropbox.com/s/gwkzxfcip...l text return value from other cell.xlsx?dl=0

 

[9191w]

Thank you! Now I see the issue. Basically the column on the right and the codes belong together, as far as I can see in your code it's the other way around.

Eg. Patric = 48629 (first line), that is his code, not the ibe if Hiroki Mizumoto. I tried changing up your code but didn't manage, apologies for that.

If you don't mind and also if you prefer that (!) I'd share the google sheet link with you via private message?

 

[Aladin Akyurek]

Thank you! Now I see the issue. Basically the column on the right and the codes belong together, as far as I can see in your code it's the other way around.

Eg. Patric = 48629 (first line), that is his code, not the ibe if Hiroki Mizumoto. I tried changing up your code but didn't manage, apologies for that.

If you don't mind and also if you prefer that (!) I'd share the google sheet link with you via private message?

In B5 enter and copy down:

=IF(ISNUMBER(FIND(".",K5)),REPLACE(K5,1,FIND(".",K5)+1,""),K5)

In E5 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($B$5:$B$15,$C5),$N$5:$N$15),"")

 

[9191w]

Thank you, but for some reason this is not working for me? Did exactly as you told

 

[Aladin Akyurek]

Thank you, but for some reason this is not working for me? Did exactly as you told

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][td]

E​

[/td][td]

K​

[/td][td]

N​

[/td][/tr][tr][td]

5​

[/td][td="bgcolor:#D9E1F2"]Patric[/td][td]Hiroki Mizumoto[/td][td="bgcolor:#D9E1F2"][/td][td]Patric[/td][td]

48629​

[/td][/tr]
[tr][td]

6​

[/td][td="bgcolor:#D9E1F2"]Miyayoshi[/td][td]Daiki Niwa[/td][td="bgcolor:#D9E1F2"][/td][td]T. Miyayoshi[/td][td]

144081​

[/td][/tr]
[tr][td]

7​

[/td][td="bgcolor:#D9E1F2"]Matsumoto[/td][td]Kazuyuki Morisaki[/td][td="bgcolor:#D9E1F2"][/td][td]T. Matsumoto[/td][td]

486368​

[/td][/tr]
[tr][td]

8​

[/td][td="bgcolor:#D9E1F2"]Aoyama[/td][td]Ryotaro Hironaga[/td][td="bgcolor:#D9E1F2"][/td][td]T. Aoyama[/td][td]

30056​

[/td][/tr]
[tr][td]

9​

[/td][td="bgcolor:#D9E1F2"]Morishima[/td][td]Hirotsugu Nakabayashi[/td][td="bgcolor:#D9E1F2"][/td][td]T. Morishima[/td][td]

283017​

[/td][/tr]
[tr][td]

10​

[/td][td="bgcolor:#D9E1F2"]Chajima[/td][td]Masato Kudo[/td][td="bgcolor:#D9E1F2"][/td][td]Y. Chajima[/td][td]

222766​

[/td][/tr]
[tr][td]

11​

[/td][td="bgcolor:#D9E1F2"]Mukuhara[/td][td]Osamu Henry Iyoha[/td][td="bgcolor:#D9E1F2"][/td][td]K. Mukuhara[/td][td]

35033​

[/td][/tr]
[tr][td]

12​

[/td][td="bgcolor:#D9E1F2"]Inagaki[/td][td]Takumi Miyayoshi[/td][td="bgcolor:#D9E1F2"]

144081​

[/td][td]S. Inagaki[/td][td]

222763​

[/td][/tr]
[tr][td]

13​

[/td][td="bgcolor:#D9E1F2"]Takahashi[/td][td]Kazuhiko Chiba[/td][td="bgcolor:#D9E1F2"]

30008​

[/td][td]S. Takahashi[/td][td]

232655​

[/td][/tr]
[tr][td]

14​

[/td][td="bgcolor:#D9E1F2"]Nogami[/td][td]Takuto Hayashi[/td][td="bgcolor:#D9E1F2"][/td][td]Y. Nogami[/td][td]

173318​

[/td][/tr]
[tr][td]

15​

[/td][td="bgcolor:#D9E1F2"]Chiba[/td][td]Nathan Burns[/td][td="bgcolor:#D9E1F2"][/td][td]K. Chiba[/td][td]

30008​

[/td][/tr]
[/table]

In B5 enter and copy down:

=IF(ISNUMBER(FIND(".",K5)),REPLACE(K5,1,FIND(".",K5)+1,""),K5)

This creates an additional range in column B for values to look up, based on values in column K.

In E5 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($B$5:$B$15,$C5),$N$5:$N$15),"")

This formula identifies the names for which we want corresponding codes.

 

[9191w]

Thank you! The problem was Google Sheets, I downloaded it on another PC with Excel and it worked, however only partially. The rows continue 1000 more downwards, so above was only a sample. The only players where it works are row 12,13 (and handful more downwards). I tried changing the range of B & N to B:B & N:N to no avail?

 

[Aladin Akyurek]

Thank you! The problem was Google Sheets, I downloaded it on another PC with Excel and it worked, however only partially. The rows continue 1000 more downwards, so above was only a sample. The only players where it works are row 12,13 (and handful more downwards). I tried changing the range of B & N to B:B & N:N to no avail?

You need to work with definite ranges, not with whole columns. Are you getting correct results when you only use the B range as is as well as the N range? If you don't, you need to explain with which input the suggestion fails.

 

[9191w]

I did that because if the name would be after D15 no code was shown. However the code only showed up for random 7/15 players. Those codes were correct but for the other rows nothing showed up. See screenshot: https://drive.google.com/file/d/0B80lZvu-Fr5bb25la2lBb19PanM/view?usp=sharing

Sorry I don't understand your last part, how do you mean? I put it in the D range btw and adapted your code accordingly as I had a code in B already.

 

[Aladin Akyurek]

I did that because if the name would be after D15 no code was shown. However the code only showed up for random 7/15 players. Those codes were correct but for the other rows nothing showed up. See screenshot: https://drive.google.com/file/d/0B80lZvu-Fr5bb25la2lBb19PanM/view?usp=sharing

Sorry I don't understand your last part, how do you mean? I put it in the D range btw and adapted your code accordingly as I had a code in B already.

Good luck, and I'm sorry as I'm not going retype your data.