Vlookup Containing Similar Text

[ismailzkhan]

Dear Techies,

I tried to find similar type of question in earlier posts but didn't find one.

Need to insert Emp code which from Sheet 2 Column B in Sheet 1 Column B matching Column A in both sheets. Problem is there is no exact matching of column A.
Attached XL file for reference.

Regards,
Ismail Khan

 

[SirJB7]

Hi, ismailzkhan!

Your data in 1st worksheet neither matches with that of 2nd worksheet, not even suppressing the first word (style treatment like Mr or Shah). Check GHUTI, it has a middle name initial that will interfere with the search.

So VLOOKUP with FALSE in 4th parameter (or INDEX & MATCH with 0 -equal- in 3rd parameter) are void. If you happen to change to TRUE or to 1 -greater- you should be sure that there won't be any case like this:

Sheet2:
MR JOHN P ROUST
MR JOHN PROUST
being both 2 different persons

Then you could set a helper column like this (field LettersOnly):
C2: =SUSTITUIR([@Employee];" ";"") -----> in english: =SUBSTITUTE([@Employee]," ","")

And issue the retrieving process with a formula like this:
Sheet1:
B2: =SI.ERROR(INDICE(Table2[Emp. ID];COINCIDIR(SUSTITUIR(DERECHA([@Employee];LARGO([@Employee])-HALLAR(" ";[@Employee]));" ";"");Table2[LettersOnly];0));"") -----> in english:
=IFERROR(INDEX(Table2[Emp. ID],MATCH(SUBSTITUTE(RIGHT([@Employee],LEN([@Employee])-SEARCH(" ",[@Employee]))," ",""),Table2[LettersOnly],0)),"")

Regards!

 

[Anant Chirmade]

Hi Ismail,

You can use the following formula


=VLOOKUP("*"&"Oma"&"*",B190:C196,2,0)

where "Oma" is the text which is in the middle of the name "Noman Shaikh".
Just replace the "Oma" with the part of text you have and apply the formula...


Alternatively, instead of typing hardcoded text "Oma" you can also give reference where the part of text is placed.

=VLOOKUP("*"&VALUE(A200)&"*",B190:C196,2,0)



Regards,

Anant

 

[SirJB7]

@Anant Chirmade
Hi!
A simple doubt: how would you generalize (i.e., normalize) the "Oma" substring selection for all entries both uniquely identifying a person and avoiding duplicate results due to partial matches?
Regards!