Partial V-Lookup in Names (Remove Middle Names)

[scorpene]

Hello,

I want to return Email IDs (C column) with respect to actual names (Column B). But I need to lookup in Username (Column A) for the Actual name keywords / Partial Match. But that Vlookup formula is not working. Is there any other method or formula that be used in this case .

In short : Return Column C based on Column B values by looking up keywords in Column A.

 

[FormR]

Hi, this seems to work for the examples given - note, you will get more people willing to take a look at your question if you post your example data using the XL2BB add-in. This allows your potential helpers to copy and paste the example data directly into Excel for testing.

Book1
	A	B	C	D
3	Aanchal Singhi	Aanchal K****ij Singhi	Aanchal.Singhi@xyz.com	Aanchal.Singhi@xyz.com
4	mr excel	mr middel name excel	mr.excel@xyz.com	mr.excel@xyz.com
5	first last	first middle last	first.last@xyz.com	first.last@xyz.com
6	albert paul	albert middle paul	albert.paul@xyz.com	albert.paul@xyz.com
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=VLOOKUP(SUBSTITUTE(A3," ","*")&"*",B:C,2,0)

 

[Peter_SSs]

Hi, this seems to work for the examples given

But could fail if one name is part of a longer name like this.

21 09 16.xlsm
	A	B	C	D
3	Aanchal Singi	Aanchal K****ijSingi	Aanchal.Singhi@xyz.com	Aanchal.Singhi@xyz.com
4	mr excellent	Mr J Excellent	mr.excellent@xyz.com	mr.excellent@xyz.com
5	mr excel	mr middle name excel	mexcel@xyz.com	mr.excellent@xyz.com
6	albert paul	albert middle paul	albert.paul@xyz.com	albert.paul@xyz.com
VLOOKUP

Cell Formulas
Range	Formula
D3:D6	D3	=VLOOKUP(SUBSTITUTE(A3," ","*")&"*",B:C,2,0)

 

[FormR]

But could fail if one name is part of a longer name like this.

Yes, it's a scenario the OP should be mindful of.

 

[scorpene]

Thank you so much !