Approximate Address Match/Search that returns multiple possible results

[jwp5151]

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:

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

Thanks in advance!

 

[pbornemeier]

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