# Approximate Address Match/Search that returns multiple possible results

#### jwp5151

##### New Member
Hello, I'm trying to perform a search of a database that searches for an approximate match of an address and provides a return of the unique identifier associated with each address match. Currently I am able to perform an exact match using the below formula which I have populated down 20 rows:

Code:
``=IFERROR(INDEX(DATA!\$F\$2:\$F\$16606, SMALL(IF(\$C\$11=DATA!\$E\$2:\$E\$16606, ROW(DATA!\$E\$2:\$E\$16606)-ROW(DATA!\$E\$2)+1), ROW(DATA!1:1))),"")``

C11 in the above formula is the cell where I would type the search string value.

Basically what I would like to be able to is type in something along the lines of "123 Elm" and have the formula search the data source for all addresses that have "123 Elm" in them regardless of whether it's Elm Drive, Elm Street, Elm Road so on so forth and then return the associated App ID number. There will be a high chance of having duplicate results where there are 3 different cases of "123 Elm Road" even though they may have a different associated App ID Number.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### pbornemeier

##### Well-known Member
I am not good enough with formulas to answer your question using them.
Would you be able to use an autofilter? If so filtering the address for 123*Elm* would show all things that started with 123 and contained Elm, including 123Elm and 123 Elm (No space and multi space)
VBA Code could also solve this

Replies
0
Views
303
Replies
4
Views
393
Replies
5
Views
478
Replies
6
Views
690
Replies
3
Views
220

1,196,010
Messages
6,012,840
Members
441,733
Latest member
MartijnB

### 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.

### Which adblocker are you using?

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

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