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 typ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    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.

Harun24HR's avatar
Harun24HR
Bronze Contributor
Aug 22, 2022

ravi0408 You may use below formula-

=INDEX($B$3:$B$4,MATCH("*"&LEFT(D3,3)&"*",$A$3:$A$4,0))

 

 

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