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))

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Search and display all cells that contain all search strings in excel
  2. Lookup with multiple criteria and display multiple search results using excel formula, part 2
  3. Search for a text string in an excel table
  4. Lookup with multiple criteria and display multiple search results using excel formula
  5. Search for multiple text strings in multiple cells in excel, part 2
  6. Filter unique distinct values where adjacent cells contain search string in excel
  7. Lookup with multiple criteria and display multiple search results using excel formula, part 3
  8. Search for multiple text strings in multiple cells in excel
  9. Lookup with multiple criteria and display multiple search results using excel formula, part 4