Fuzzy Lookup Meets Find and Replace

[damaniam1604]

So first things first, my title might be inaccurate but I'm not sure of the terminology. I have a long list within a single column of various length text. Most of these text strings will contain, in part, 1 of about 30 known phrases. In the column next to the original text I'd like to place the related phrase. As an example, A1 might contain "bank item paid check #130 " and the list of known phrase will contain paid check. I'd like to have B1 filled with the partially matched known phrase of "paid check". Would this be an IF function, a Match function, some sort of isnumber(search()) function? I could use some help and I and grateful in advance.

Thanks,
-J

 

[DanteAmor]

Hi @damaniam1604,

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:292.75px;" /><col style="width:120.71px;" /><col style="width:26.61px;" /><col style="width:26.61px;" /><col style="width:191.05px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; text-align:center; ">Text</td><td style="background-color:#92d050; text-align:center; ">Result</td><td > </td><td > </td><td style="background-color:#92d050; ">known phrase</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >bank item paid check #130</td><td >paid check</td><td > </td><td > </td><td >paid check</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >a single column of various length text</td><td >length text</td><td > </td><td > </td><td >Letter delivered</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td >length text</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IFERROR(LOOKUP(9.99E+307,SEARCH(E$2:E$4,A2),E$2:E$4),"")</td></tr></table></td></tr></table>

 

[damaniam1604]

Much appreciated. That worked perfectly. If you wouldn't mind explaining a bit of the logistics behind why that formula works, I would love to hear an explanation or if you can point me to a resource. That said, thanks again for your help.

 

[DanteAmor]

Much appreciated. That worked perfectly. If you wouldn't mind explaining a bit of the logistics behind why that formula works, I would love to hear an explanation or if you can point me to a resource. That said, thanks again for your help.

9.99E+307 is the largest number that Excel can store. This "Big number" is used to look up the last number in a range.


The following example searches for the largest number in the range C12:C15.
In this case the largest number is 4, then the LOOKUP function returns "d"

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:120.71px;" /><col style="width:26.61px;" /><col style="width:26.61px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >d</td><td style="text-align:right; ">1</td><td >a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td style="text-align:right; ">2</td><td >b</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td style="text-align:right; ">3</td><td >c</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="text-align:right; ">4</td><td >d</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B12</td><td >=LOOKUP(9.99E+307,C12:C15,D12:D15)</td></tr></table></td></tr></table>


Then

SEARCH(E$2:E$4,A2)

The SEARCH function searches the texts of each phrase in the range E2:E4 within the text of cell A2.
SEARCH returns the number of characters in which a text string is found.

LOOKUP(9.99E+307,SEARCH(E$2:E$4,A2),E$2:E$4)
LOOKUP(9.99E+307,SEARCH({"paid check","Letter delivered","length text"},A2),E$2:E$4)
LOOKUP(9.99E+307,SEARCH({"paid check","Letter delivered","length text"},bank item paid check #130),E$2:E$4)
LOOKUP(9.99E+307,{11,#¡VALUE!,#¡VALUE!,E$2:E$4)
LOOKUP(9.99E+307,{11,#¡VALUE!,#¡VALUE!},{"paid check","Letter delivered","length text"})
The largest number is 11
Then result is "paid check"

If the value is not found then return

{#¡VALUE!,#¡VALUE!,#¡VALUE!}

And that's what the error function is for

=IFERROR(LOOKUP(9.99E+307,SEARCH(E$2:E$4,A2),E$2:E$4),"")
=IFERROR({#¡VALUE!,#¡VALUE!,#¡VALUE!},"")
=""

I hope this helps you.

<colgroup><col></colgroup><tbody>
</tbody>

 

[Jono_NZ]

@DanteAmor - just found this for the exact same need. Life saver!!!

 

[DanteAmor]

@Jono_NZ, Glad to hear it works for you. I take this opportunity to put the formula with XL2BB Tool.

Dante Amor
	A	B	C	D	E
1	Text	Result			known phrase
2	bank item paid check #130	paid check			paid check
3	a single column of various length text	length text			Letter delivered
4					length text
Hoja12

Cell Formulas
Range	Formula
B2:B3	B2	=IFERROR(LOOKUP(9.99E+307,SEARCH(E$2:E$4,A2),E$2:E$4),"")

__________________________________________________________________________________

Dante Amor
	B	C	D
12	d	1	a
13		2	b
14		3	c
15		4	d
Hoja12

Cell Formulas
Range	Formula
B12	B12	=LOOKUP(9.99E+307,C12:C15,D12:D15)