Partial Match in Excel

[AHS5050]

Hello,
I receive raw data from our central system that has over thousands of rows of data each month. I need to clean the data manually to show which company each row belongs to. It a tedious work that takes hours.

I am hoping to be able to search names in a cell/cells with long texts. For example, I search for the words "Mike", "Steve", "Key", "Tim", (A12 to A16) in cells B3 to B9. If Excel finds a match, then puts the corresponding simple Name from B12 to B16, as shown in Column F. For example, I want to search for "Mike" in a cell that contains "Mike Miller Associates PY Adj" or "Reclass for Mike Miller for CY", etc. Please see the screen shot below.

Would you please help in creating a formula (in the yellow column) that will put the Complete Name of each company in the yellow cells based on a Search Key table?

Thank you,
Haseeb

 

[jtakw]

Hi,

Please consider uploading sample using XL2BB (see my signature), or at least in table format, so helpers don't Have to manually type up data.

BTW, your written requirements differs from the requirements in your picture attachments, this is based on your Written description.

Try this:

Book3.xlsx
	A	B	C	D
1
2
3		Steve abc def ghi		Steve Fox Ltd
4		Mike abc def ghi		Mike Miller Associates
5		Miss-coding Jasson Key		Jasson Key Superstore
6		Mike xyz jkl		Mike Miller Associates
7		Steve iop wer yuu		Steve Fox Ltd
8		Nov 2021 Elsa and Tim		Elsa and Tim
9		Refund Jasson Key		Jasson Key Superstore
10		abc Walmart
11
12
13	Mike	Mike Miller Associates
14	Steve	Steve Fox Ltd
15	Key	Jasson Key Superstore
16	Tim	Elsa and Tim
Sheet995

Cell Formulas
Range	Formula
D3:D10	D3	=IFERROR(LOOKUP(2,1/SEARCH(A$13:A$16,B3),B$13:B$16),"")

 

[AHS5050]

Thank you jtakw. I appreciate it.

I tried to simplify the question thinking it may not be possible to use Excel for my issue, but your skill proved me wrong. I actually get the raw data with notes in 5 different columns. Each of them could contain the keyword. So if the keyword cannot be found in one column, the formula looks at the next one until it finds the key word. I tried to use your solution and apply it to other columns but failed. I hope I am not being a pain here. Is it possible to expand the formula over 5 columns?

Partial Match.xlsx
	A	B	C	D	E	F	G
1
2		Note 1	Note 2	Note 3	Note 4	Note 5	Keys
3		Steve abc def ghi					Steve Fox Ltd
4		Sam abc def ghi	Mike abc def ghi kl	abc	def	def ty76
5		Miss-coding Jasson Key					Jasson Key Superstore
6		Tom xyz jkl	klp	Mike xyz jkl	56ip	56ip 90sadf
7		Stevenson iop wer yuu		Alex rt5		Steve iop wer yuu	Steve Fox Ltd
8		Nov 2021 Elsa and Andy			Nov 2021 Elsa and Tim
9		Refund Jasson Key					Jasson Key Superstore
10		abc Walmart
11
12
13
14	Name	Complete Name
15	Mike	Mike Miller Associates
16	Steve	Steve Fox Ltd
17	Key	Jasson Key Superstore
18	Tim	Elsa and Tim
SAP 2021 (2)

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(LOOKUP(2,1/SEARCH('SAP 2021 (2)'!A$15:A$18,B3),'SAP 2021 (2)'!B$15:B$18),"")

 

[Peter_SSs]

Welcome to the MrExcel board!

.. and thanks for using XL2BB this time.

Firstly, with your original sample, the formula suggested by @jtakw could possibly return incorrect results - see cell D10 below.

AHS5050.xlsm
	A	B	C	D
1
2
3		Steve abc def ghi		Steve Fox Ltd
4		Mike abc def ghi		Mike Miller Associates
5		Miss-coding Jasson Key		Jasson Key Superstore
6		Mike xyz jkl		Mike Miller Associates
7		Steve iop wer yuu		Steve Fox Ltd
8		Nov 2021 Elsa and Tim		Elsa and Tim
9		Refund Jasson Key		Jasson Key Superstore
10		Refund from Stimson Ltd		Elsa and Tim
11
12
13	Mike	Mike Miller Associates
14	Steve	Steve Fox Ltd
15	Key	Jasson Key Superstore
16	Tim	Elsa and Tim
Sheet1

Cell Formulas
Range	Formula
D3:D10	D3	=IFERROR(LOOKUP(2,1/SEARCH(A$13:A$16,B3),B$13:B$16),"")

Adjusting @jtakw's formula to stop that and allowing for the multiple columns, try this.

AHS5050.xlsm
	A	B	C	D	E	F	G
1
2		Note 1	Note 2	Note 3	Note 4	Note 5	Keys
3		Steve abc def ghi					Steve Fox Ltd
4		Sam abc def ghi	Mike abc def ghi kl	abc	def	def ty76	Mike Miller Associates
5		Miss-coding Jasson Key					Jasson Key Superstore
6		Tom xyz jkl	klp	Mike xyz jkl	56ip	56ip 90sadf	Mike Miller Associates
7		Stevenson iop wer yuu		Alex rt5		Steve iop wer yuu	Steve Fox Ltd
8		Nov 2021 Elsa and Andy			Nov 2021 Elsa and Tim		Elsa and Tim
9		Refund Jasson Key					Jasson Key Superstore
10		abc Walmart
11
12
13
14	Name	Complete Name
15	Mike	Mike Miller Associates
16	Steve	Steve Fox Ltd
17	Key	Jasson Key Superstore
18	Tim	Elsa and Tim
Sheet2

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(LOOKUP(2,1/SEARCH(" "&A$15:A$18&" "," "&TEXTJOIN(" ",1,B3:F3)&" "),B$15:B$18),"")

One further point: It is a bad idea to use the sheet name of the sheet that the formula is in like you have in your formula in post #3
That is, using the following formula in sheet 'SAP 2021 (2)' can lead to problems.
=IFERROR(LOOKUP(2,1/SEARCH('SAP 2021 (2)'!A$15:A$18,B3),'SAP 2021 (2)'!B$15:B$18),"")

 

[Peter_SSs]

Also, if there could be any sort of overlap with the short names, like in cells A5 & A18 below, the previous formula could still return dubious results as shown in G4 below.
An alternative is shown in column H that for a situation like that would list all possible matches and you could AutoFilter that columns for "contains" "/" to manually enter the correct results.

AHS5050.xlsm
	A	B	C	D	E	F	G	H
1
2		Note 1	Note 2	Note 3	Note 4	Note 5	Keys	Keys
3		Steve abc def ghi					Steve Fox Ltd	Steve Fox Ltd
4		Sam abc def ghi	Mike Miller abc	abc	def	def ty76	Ken & Mike	Mike Miller Associates / Ken & Mike
5		Miss-coding Jasson Key					Jasson Key Superstore	Jasson Key Superstore
6		Tom xyz jkl	klp	Mike xyz jkl	56ip	56ip 90sadf	Ken & Mike	Ken & Mike
7		Stevenson iop wer yuu		Alex rt5		Steve iop wer yuu	Steve Fox Ltd	Steve Fox Ltd
8		Nov 2021 Elsa and Andy			Nov 2021 Elsa and Tim		Elsa and Tim	Elsa and Tim
9		Refund Jasson Key					Jasson Key Superstore	Jasson Key Superstore
10		abc Walmart
11
12
13
14	Name	Complete Name
15	Mike Miller	Mike Miller Associates
16	Steve	Steve Fox Ltd
17	Key	Jasson Key Superstore
18	Mike	Ken & Mike
19	Tim	Elsa and Tim
Sheet3

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(LOOKUP(2,1/SEARCH(" "&A$15:A$19&" "," "&TEXTJOIN(" ",1,B3:F3)&" "),B$15:B$19),"")
H3:H10	H3	=TEXTJOIN(" / ",1,FILTER(B$15:B$19,ISNUMBER(SEARCH(" "&A$15:A$19&" "," "&TEXTJOIN(" ",1,B3:F3)&" ")),""))

 

[jtakw]

Thank you jtakw. I appreciate it.

I tried to simplify the question thinking it may not be possible to use Excel for my issue, but your skill proved me wrong. I actually get the raw data with notes in 5 different columns. Each of them could contain the keyword. So if the keyword cannot be found in one column, the formula looks at the next one until it finds the key word. I tried to use your solution and apply it to other columns but failed. I hope I am not being a pain here. Is it possible to expand the formula over 5 columns?

Partial Match.xlsx
	A	B	C	D	E	F	G
1
2		Note 1	Note 2	Note 3	Note 4	Note 5	Keys
3		Steve abc def ghi					Steve Fox Ltd
4		Sam abc def ghi	Mike abc def ghi kl	abc	def	def ty76
5		Miss-coding Jasson Key					Jasson Key Superstore
6		Tom xyz jkl	klp	Mike xyz jkl	56ip	56ip 90sadf
7		Stevenson iop wer yuu		Alex rt5		Steve iop wer yuu	Steve Fox Ltd
8		Nov 2021 Elsa and Andy			Nov 2021 Elsa and Tim
9		Refund Jasson Key					Jasson Key Superstore
10		abc Walmart
11
12
13
14	Name	Complete Name
15	Mike	Mike Miller Associates
16	Steve	Steve Fox Ltd
17	Key	Jasson Key Superstore
18	Tim	Elsa and Tim
SAP 2021 (2)

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(LOOKUP(2,1/SEARCH('SAP 2021 (2)'!A$15:A$18,B3),'SAP 2021 (2)'!B$15:B$18),"")

I was just trying to answer OP original question as best as I can I understand, I anticipated further inquiries

BTW, your written requirements differs from the requirements in your picture attachments, this is based on your Written description.

Seems like you got it covered

 

[jtakw]

Firstly, with your original sample, the formula suggested by @jtakw could possibly return incorrect results - see cell D10 below.

B10 was not in OP's original sample, otherwise I would've added the spaces in my formula.

Also, if there could be any sort of overlap with the short names, like in cells A5 & A18 below, the previous formula could still return dubious results as shown in G4 below.
An alternative is shown in column H that for a situation like that would list all possible matches and you could AutoFilter that columns for "contains" "/" to manually enter the correct results.

I don't have TEXTJOIN as Peter_SSs and OP has, so my formula manually joins the Text in the 5 cells.
As for the possible Overlap of short names pointed out by Peter_SSs, I also recommend using Unique Name Keys in A15:A19, (e.g. use "Ken" for "Ken and Mike")

Book3.xlsx
	A	B	C	D	E	F	G
1
2		Note 1	Note 2	Note 3	Note 4	Note 5	Keys
3		Steve abc def ghi					Steve Fox Ltd
4		Sam abc def ghi	Mike Miller abc	abc	def	def ty76	Mike Miller Associates
5		Miss-coding Jasson Key					Jasson Key Superstore
6		Tom xyz jkl	klp	Ken & Mike xyz jkl	56ip	56ip 90sadf	Ken & Mike
7		Stevenson iop wer yuu		Alex rt5		Steve iop wer yuu	Steve Fox Ltd
8		Nov 2021 Elsa and Andy			Nov 2021 Elsa and Tim		Elsa and Tim
9		Refund Jasson Key					Jasson Key Superstore
10		abc Walmart
11
12
13
14	Name	Complete Name
15	Mike	Mike Miller Associates
16	Steve	Steve Fox Ltd
17	Key	Jasson Key Superstore
18	Ken	Ken & Mike
19	Tim	Elsa and Tim
Sheet995

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(LOOKUP(2,1/SEARCH(" "&A$15:A$19&" "," "&B3&" "&C3&" "&D3&" "&E3&" "&F3&" "),B$15:B$19),"")

 

[Peter_SSs]

B10 was not in OP's original sample, otherwise I would've added the spaces in my formula.

By 'original sample' I was actually referring to the layout of a single column of text to check, rather than the actual values which are clearly not real.
In any case my point was that short strings especially, like "key" or "tim", could easily appear in longer words in the data & therefore it would be prudent to include the spaces.

 

[AHS5050]

Thank you Peter_SSs and jtakw. You guys are awesome.

I applied Peter_SSs' solution but for some reason the result comes blank. Maybe my Excel doesn't support TextJoin, but not sure since I am still learning basic Excel. jtakw's latest solution works with one problem that is caused by inconsistent manual data entries in our system.

The problem is that some Key words have been entered without any space with the proceeding/following words/characters. ie. Steve--Fox instead of Steve Fox or MikeMiller instead of Mike Miller. The solution doesn't find these ones. Is there any tweak in the solution that can fix this problem?

Thank you again.

 

[Peter_SSs]

Maybe my Excel doesn't support TextJoin

Test in a blank cell with
=TEXTJOIN(1,1,1)
If that comes back with 1 then you have TEXTJOIN.
If it comes up with #NAME? then you do not have TEXTJOIN. In that case you should edit your profile because that says you have Microsoft 365 which does have TEXTJOIN.