How to create a useful search formula
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:
Leave a Reply