Fuzzy Lookup Meets Find and Replace

damaniam1604

New Member
Joined
Sep 12, 2018
Messages
20
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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>
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.




[TABLE="width: 191"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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

Dante Amor
ABCDE
1TextResultknown phrase
2bank item paid check #130paid checkpaid check
3a single column of various length textlength textLetter delivered
4length text
Hoja12
Cell Formulas
RangeFormula
B2:B3B2=IFERROR(LOOKUP(9.99E+307,SEARCH(E$2:E$4,A2),E$2:E$4),"")


__________________________________________________________________________________
Dante Amor
BCD
12d1a
132b
143c
154d
Hoja12
Cell Formulas
RangeFormula
B12B12=LOOKUP(9.99E+307,C12:C15,D12:D15)
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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
Back
Top