SOLVED

Searching Impartial Values from one List on another

%3CLINGO-SUB%20id%3D%22lingo-sub-1306439%22%20slang%3D%22en-US%22%3ESearching%20Impartial%20Values%20from%20one%20List%20on%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1306439%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Good%20people.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20has%20been%20keeping%20me%20up%20at%20night%3A%20I%20have%20two%20lists%20of%20names%2C%20one%20with%20full%20names%20and%20other%20with%20partial%20names%20and%20related%20email%20address.%20Example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EList%201%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSTRONG%3EName%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSTRONG%3EEmail%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EJames%20Cleveland%20Owens%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EWalter%20Elias%20Disney%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EList%202%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSTRONG%3EName%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSTRONG%3Eemail%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EJames%20Owens%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EJames.owens%40company.com%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EWalter%20Disney%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3Ewalter.disney%40company.com%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20to%20do%20is%20populate%20the%20Email%20column%20on%20list%20one%20with%20the%20emails%20on%20list%202.%20I%20need%20excel%20to%20identify%20Jesse%20Cleveland%20Owens%20and%20Jesse%20Owens%20as%20the%20same%20to%20give%20me%20the%20corresponding%20email.%20I%20tried%20to%20use%20vlookup%20with%20TRUE%20for%20an%20approximate%20match%2C%20but%20since%20my%20actual%20database%20has%20several%20%22James%22%2C%20the%20returned%20value%20ends%20up%20being%20the%20name%20and%20email%20of%20a%20different%20James.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20could%20help%20me%20out%20here%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20to%20all%2C%20and%20stay%20safe!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1306439%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1306472%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20Impartial%20Values%20from%20one%20List%20on%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1306472%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F621250%22%20target%3D%22_blank%22%3E%40KawanMSC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20clarify%20names%20in%20first%20table%20are%20ALWAYS%20have%203%20parts%2C%20or%20that%20could%20be%20something%20like%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EWalter%20Elias%20Disney%20Jr%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EWalter%20Elias%20Disney%20Sr%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EJoe%20Doe%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1306502%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20Impartial%20Values%20from%20one%20List%20on%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1306502%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe're%20talking%20Brazilian%20names%20here%20actually%2C%20so%20they%20vary%20between%203%20parters%2C%204%20parters%20or%20even%205%20parters.%20But%20the%20combination%20on%20List%202%20is%20always%20unique.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps%2C%20and%20thank%20fro%20such%20a%20quick%20response.%26nbsp%3B%3C%2FP%3E%3CP%3EBest!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1306541%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20Impartial%20Values%20from%20one%20List%20on%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1306541%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F621250%22%20target%3D%22_blank%22%3E%40KawanMSC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you.%20But%20perhaps%20there%20is%20some%20other%20logic%2C%20e.g.%20first%20and%20last%20part%20are%20to%20be%20taken%20to%20compare%20with%20second%20list%2C%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EWalter%20Elias%20Mark%20Disney%20%3D%26gt%3B%20Walter%20Disney%20and%20never%20Walter%20Mark%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EOr%20there%20is%20no%20common%20logic%20at%20all%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1306569%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20Impartial%20Values%20from%20one%20List%20on%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1306569%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20no%20common%20logic%2C%20because%20we%20could%20have%20two%20people%20named%3C%2FP%3E%3CP%3EWalter%20Elias%20Mark%20Disney%20%3D%26gt%3B%20Walter%20Disney%20(First%20%2B%20Last)%3C%2FP%3E%3CP%3EWalter%20Bob%20Patrick%20Disney%20%3D%26gt%3B%20Walter%20Patrick%20(First%20%2B%20Third)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20happens%20because%20many%20people%20will%20have%20similar%20last%20and%20middle%20names%20here%20and%20the%20company%20has%20over%20a%20thousand%20employees%2C%20so%20you%20can%20see%20how%20there%20would%20be%20lots%20of%20these%20similarities.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20realize%20this%20may%20end%20up%20being%20a%20case%20where%20I%20will%20need%20to%20check%20by%20hand%2C%20but%20I%20want%20to%20exhaust%20my%20options.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebest%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1306641%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20Impartial%20Values%20from%20one%20List%20on%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1306641%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F621250%22%20target%3D%22_blank%22%3E%40KawanMSC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20try%20something%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20372px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F184334i982929E91F08B1D1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20C3%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20INDEX(%24G%243%3A%24G%249%2C%0A%20%20%20%20%20MATCH(1%2C%0A%20%20%20%20%20%20%20%20INDEX(ISNUMBER(SEARCH(%24E%243%3A%24E%249%2CB3))*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20ISNUMBER(SEARCH(%24F%243%3A%24F%249%2CB3))%2C0%0A%20%20%20%20%20%20%20%20)%2C0%0A%20%20%20%20%20)%0A)%2C%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down.%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1307315%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20Impartial%20Values%20from%20one%20List%20on%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1307315%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESir%2C%20you%20are%20amazing.%20Thank%20you%20so%20**bleep**%20much%20for%20this%20it%20was%20of%20great%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKeep%20up%20the%20great%20work!%3C%2FP%3E%3CP%3EBest%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1308438%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20Impartial%20Values%20from%20one%20List%20on%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1308438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F621250%22%20target%3D%22_blank%22%3E%40KawanMSC%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello Good people. 

 

Here is what has been keeping me up at night: I have two lists of names, one with full names and other with partial names and related email address. Example:

 

List 1: 

NameEmail
James Cleveland Owens 
Walter Elias Disney 

 

List 2:

Nameemail
James OwensJames.owens@company.com
Walter Disneywalter.disney@company.com

 

What I need to do is populate the Email column on list one with the emails on list 2. I need excel to identify Jesse Cleveland Owens and Jesse Owens as the same to give me the corresponding email. I tried to use vlookup with TRUE for an approximate match, but since my actual database has several "James", the returned value ends up being the name and email of a different James. 

 

Anyone could help me out here?

 

Best to all, and stay safe!

7 Replies
Highlighted

@KawanMSC 

Could you please clarify names in first table are ALWAYS have 3 parts, or that could be something like

 

Walter Elias Disney Jr

Walter Elias Disney Sr

Joe Doe

 

 

 

Highlighted

Hello @Sergei Baklan ,

 

We're talking Brazilian names here actually, so they vary between 3 parters, 4 parters or even 5 parters. But the combination on List 2 is always unique. 

 

Hope this helps, and thank fro such a quick response. 

Best!

 

Highlighted

@KawanMSC 

Thank you. But perhaps there is some other logic, e.g. first and last part are to be taken to compare with second list, like

Walter Elias Mark Disney => Walter Disney and never Walter Mark

 

Or there is no common logic at all?

Highlighted

Hello @Sergei Baklan ,

 

There is no common logic, because we could have two people named

Walter Elias Mark Disney => Walter Disney (First + Last)

Walter Bob Patrick Disney => Walter Patrick (First + Third)

 

That happens because many people will have similar last and middle names here and the company has over a thousand employees, so you can see how there would be lots of these similarities. 

 

I realize this may end up being a case where I will need to check by hand, but I want to exhaust my options. 

 

best,

 

Highlighted
Best Response confirmed by KawanMSC (New Contributor)
Solution

@KawanMSC 

You may try something like this

image.png

In C3

=IFERROR(
  INDEX($G$3:$G$9,
     MATCH(1,
        INDEX(ISNUMBER(SEARCH($E$3:$E$9,B3))*
              ISNUMBER(SEARCH($F$3:$F$9,B3)),0
        ),0
     )
),"no such")

and drag it down.

Please check in attached file.

Highlighted

Hello @Sergei Baklan 

 

Sir, you are amazing. Thank you so **bleep** much for this it was of great help. 

 

Keep up the great work!

Best

Highlighted

@KawanMSC , you are welcome, glad to help