vlookup part of a cell in part of another cell

[mohammadmihdi]

Hi,

I hope this note finds you well. I want to vlookup a part of value in part of cell data.

For example I have a list of full names in one column and have another column that contains a part of the name meaning the first OR last name.

So partial value vlookup on parial data is possible?



So if in A1 I have “john smith” I know that =(“*”&A1&”*”,B1,1,false) will not work if B1 data is “M. smith company” or “john company”.

I need to vlookup by part of the name. Thank You very much.

 

[Abid Fattani]

Following article may help.

Partial match with VLOOKUP

To retrieve information from a table based on a partial match, you can use the VLOOKUP function in exact match mode with a wildcard. In the example shown, the formula in H7 is: =VLOOKUP(value&"*",data,2,FALSE) where value (H4) and data (B5:E104) are named ranges.

exceljet.net

 

[mohammadmihdi]

the formula in that thread is already used and not effective.

 

[mohammadmihdi]

the formula =vlookup(“*”&A1&”*”,B1,1,false) would work if I vlooup for A1 in parts of B1. But I want to vlookup parts of A1 in parts of B1.

 

[jtakw]

Hi,

If I understand correctly, if you have "john smith" in A1, you want to Lookup Column B for values that contain either "john" or "smith" and return that value, Correct?
These formulas would do that, I see potential problems though:
1. Do you want "john" as primary search, or "smith" as primary search?
My formulas, are using "john" 1st, if not found, than "smith"
2. What if you have both matches, (i.e. “M. smith company” and “john company”)?
My formulas, will return the Last found in Column.

The Longer version is Normally entered, the Shorter version requires CSE:

Book3.xlsx
	A	B	C	D	E
1	john smith	abc corp		M. smith company	M. smith company
2		M. smith company
3		xyz company
4
5
6
Sheet924

Cell Formulas
Range	Formula
D1	D1	=IFERROR(IFERROR(LOOKUP(2,1/SEARCH(LEFT(A1,FIND(" ",A1)-1),B1:B10),B1:B10),LOOKUP(2,1/SEARCH(MID(A1,FIND(" ",A1)+1,99),B1:B10),B1:B10)),"None")
E1	E1	=IFERROR(LOOKUP(2,1/IFERROR(SEARCH(LEFT(A1,FIND(" ",A1)-1),B1:B10),SEARCH(MID(A1,FIND(" ",A1)+1,99),B1:B10)),B1:B10),"None")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mohammadmihdi]

No this did not work. Please see the image. I want to vlookup data set 1 customers in data set 2 customers and input balance.

 

[jtakw]

It didn't work because your description in OP does Not match your pic above.
Column A has Middle Initials, Middle Names, and Other Text within.
And you didn't say to return the Value of a Different Column.

You'll have to decide How you want the criteria First.
It will be impossible to lookup Every single word of the entire Text strings of Column A, may be Just the First name?
But looking at your sample, "Aaron" in Column A has Numerous matches in Column E, so That won't work.