Partial V-Lookup in Names (Remove Middle Names)

scorpene

New Member
Joined
Aug 2, 2019
Messages
11
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.

1631780861407.png
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,594
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
3Aanchal SinghiAanchal Kshitij SinghiAanchal.Singhi@xyz.comAanchal.Singhi@xyz.com
4mr excelmr middel name excelmr.excel@xyz.commr.excel@xyz.com
5first lastfirst middle lastfirst.last@xyz.comfirst.last@xyz.com
6albert paulalbert middle paulalbert.paul@xyz.comalbert.paul@xyz.com
Sheet1
Cell Formulas
RangeFormula
D3:D6D3=VLOOKUP(SUBSTITUTE(A3," ","*")&"*",B:C,2,0)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,708
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
3Aanchal SingiAanchal KshitijSingiAanchal.Singhi@xyz.comAanchal.Singhi@xyz.com
4mr excellentMr J Excellentmr.excellent@xyz.commr.excellent@xyz.com
5mr excelmr middle name excelmexcel@xyz.commr.excellent@xyz.com
6albert paulalbert middle paulalbert.paul@xyz.comalbert.paul@xyz.com
VLOOKUP
Cell Formulas
RangeFormula
D3:D6D3=VLOOKUP(SUBSTITUTE(A3," ","*")&"*",B:C,2,0)
 

Forum statistics

Threads
1,147,498
Messages
5,741,504
Members
423,663
Latest member
kaveh87rsh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top