Forum Discussion

Copper Contributor

Aug 22, 2022

Solved

Prefix Name Match with starting name along with added suffixes characters.

Hi, i have a list of some names and thier new name replacement data. I want to lookup in a range where the real name have some added suffixes characters. Is there any formula which can be typ...

excel

Formulas and Functions

HansVogelaar
Aug 22, 2022
ravi0408

How about

=XLOOKUP(1,XMATCH($A$3:$A$10&"*",D3,2),$B$3:$B$10,"")

in E3, the fill down.

Jihad Al-Jarady

MCT

Aug 22, 2022

Hi ravi0408

Is there a rule or pattern for the prefix?
I can see that the first name in your result table is 3 characters, while the forth one is 7 characters, and they are referring to the same name!
I will take the first 3 characters and search for the name in the table and bring the replacement using this formula:

=VLOOKUP(LEFT(E3,3),$A$3:$B$4,2,TRUE)

ravi0408
Copper Contributor
Aug 22, 2022
That's not working. i want full name to be matched avoiding suffix from sample name then replace with new name.

Also see the latest example sheet to gain more in the problem.

Thanks
- HansVogelaar
  MVP
  Aug 22, 2022
  ravi0408
  
  How about
  
  =XLOOKUP(1,XMATCH($A$3:$A$10&"*",D3,2),$B$3:$B$10,"")
  
  in E3, the fill down.
  - ravi0408
    Copper Contributor
    Aug 23, 2022
    Thanks. this works well.