Question: How do i create a flexible search formula to search a list?

Sheet1

Sheet "Addresses"

Answer: Here is a useful formula.

=INDEX(Addresses!A2:A9,SMALL(IF(ISERROR(IF(SEARCH(C3,Addresses!A2:A5),ROW(Addresses!A2:A5),"")),"", IF(SEARCH(C3;Addresses!A2:A5),ROW(Addresses!A2:A5),"")),ROW()-10)-1)) + Ctrl + Shift +Enter

SEARCH(C3,Addresses!A2:A5) This part of the formula searches the rows "Addresses!A2:A5" for cell value in C3. C3 is our search field. In this case it is "s". It returns the number of the character at which a specific character or text string is first found, reading left to right.
{#Value, 11,#Value,1,#NA,#NA}

IF(SEARCH(C3,Addresses!A2:A5),ROW(Addresses!A2:A5),"") This part returns the row number at which a number of character is first found.
{#Value, 2,#Value,4,#NA,#NA}

IF(ISERROR(IF(SEARCH(C3,Addresses!A2:A5),ROW(Addresses!A2:A5),"")),"", IF(SEARCH(C3;Addresses!A2:A5),ROW(Addresses!A2:A5),"") This part removes the errors and replaces them with nothing "".
{"", 2,"",4,"",""}

SMALL(IF(ISERROR(IF(SEARCH(C3,Addresses!A2:A5),ROW(Addresses!A2:A5),"")),"", IF(SEARCH(C3;Addresses!A2:A5),ROW(Addresses!A2:A5),"")),ROW()-10)-1) Removes the "" and orders the array ascending.
{ 2,4}

INDEX(Addresses!A2:A9,SMALL(IF(ISERROR(IF(SEARCH(C3,Addresses!A2:A5), ROW(Addresses!A2:A5),"")),"",IF(SEARCH(C3;Addresses!A2:A5),ROW(Addresses!A2:A5),"")),ROW()-10)-1))
{Harvey Leeson,Steven Gold}

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Search() - Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

SMALL() returns the k-th smallest row number in this data set.

If you want to do a case sensitive search, use find() instead of search().

=IF(ISERROR(INDEX(Addresses!A2:A9,SMALL(IF(ISERROR(IF( SEARCH(C3,Addresses!A2:A5),ROW(Addresses!A2:A5),"")),"",IF( SEARCH(C3,Addresses!A2:A5),ROW(Addresses!A2:A5),"")),ROW()-10)-1)),"", INDEX(Addresses!A2:A9,SMALL(IF(ISERROR(IF(SEARCH(C3,Addresses!A2:A5), ROW(Addresses!A2:A5),"")),"",IF(SEARCH(C3,Addresses!A2:A5),ROW(Addresses!A2:A5),"")),ROW()-10)-1))

Related posts:

Text to columns: Split words in a cell (excel array formula)

Lookup with multiple criteria and display multiple search results using excel formula, part 2

Search and display all cells that contain all search strings in excel

Lookup with multiple criteria and display multiple unique search results (array formula)

Lookup with multiple criteria and display multiple search results using excel formula