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]

No worries. Could you explain why I need to use definite ranges? I increased the range size from 15 to 25 and it worked with all (except the ones beyond 25). As the soccer teams are in blocks of about 30 players that already saves me a lot of work. If I increase beyond 25 it doesn't work however, why? Any tip?

 

[9191w]

Nevermind, I played around with it a bit and actually managed to get it to output 95% of everything. Increasing the cell range did most of the job.

Thanks a lot for your help, this saved me weeks of doing this manually.

 

[Aladin Akyurek]

No worries. Could you explain why I need to use definite ranges? I increased the range size from 15 to 25 and it worked with all (except the ones beyond 25). As the soccer teams are in blocks of about 30 players that already saves me a lot of work. If I increase beyond 25 it doesn't work however, why? Any tip?

1. Let's assume that the data is located in B:N of Sheet1 (Adjust the sheet name to suit.).

2.Define Lrow in Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$N:$N)

3. Define Crange in Name Manager as referring to:

=Sheet1!$C$5:INDEX(Sheet1!$C:$C,Lrow)

4. Define Drange in Name Manager as referring to:

=Sheet1!$D$5:INDEX(Sheet1!$D:$D,Lrow)

5. Define Erange in Name Manager as referring to:

=Sheet1!$E$5:INDEX(Sheet1!$E:$E,Lrow)

6. Define Krange in Name Manager as referring to:

=Sheet1!$K$5:INDEX(Sheet1!$K:$K,Lrow)

7. Define Nrange in Name Manager as referring to:

=Sheet1!$N$5:INDEX(Sheet1!$N:$N,Lrow)

8. In D5 enter and copy down:

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

9. In E5 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Drange,$C5),Nrange),"")