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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,623
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
52,233
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)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,575
Messages
5,765,188
Members
425,266
Latest member
CPAgirl

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