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.