Forum Discussion

ravi0408's avatar
ravi0408
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 typed in H2 cell to lookup by prefix name and then replaced by new name. Below is the sampl

 

Pls help

 

excel
Formulas and Functions

6 Replies

Sort By
  • Jihad Al-Jarady's avatar
    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's avatar
      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's avatar
        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's avatar
      ravi0408
      Copper Contributor
      Aug 22, 2022

      Harun24HR Thanks for your prompt solution. I really appriciate it.

       

      but you have used only 3 character from left to lookup. I want the text should exact match with the name list then replace it with the new name.

       

      another example is also attached. please resolve it.

       

       

Resources