Help making field BLANK/EMPTY when personalized search field is empty

[Mmeneses]

Hello
I used this formula to help me find a full match by writing down a partial string in an empty cell next to a contact list of the company I'm working for.

=IF(ISERROR(
INDEX($A$6:$A$153,IF(COUNTIF($A$6:$A$153,"*"&$F$6&"*")>=ROWS($F$6:$F6),SMALL(IF(ISNUMBER(SEARCH($F$6,$A$6:$A$153)),ROW($A$6:$A$153),""),ROWS($F$6:$F6))-5,""),1)),"",
INDEX($A$6:$A$153,IF(COUNTIF($A$6:$A$153,"*"&$F$6&"*")>=ROWS($F$6:$F6),SMALL(IF(ISNUMBER(SEARCH($F$6,$A$6:$A$153)),ROW($A$6:$A$153),""),ROWS($F$6:$F6))-5,""),1))

(pressed ctrl,shift,enter)

It worked! and I dragged the formula down so it would show all matches (like when typing max I would get in cell 1 maximiliano, the one below maxwell, etc). However, when I would leave the cell the formula used to do the search blank, it would fill in alphabetically (so in field it was blank ("") and in cell 1 I would get anna, then below brian, etc.).
Is there any way that I could make it so that when the search cell is blank, the results column will also be blank?

 

[Kuljack]

Hello
I used this formula to help me find a full match by writing down a partial string in an empty cell next to a contact list of the company I'm working for.

=IF(ISERROR(
INDEX($A$6:$A$153,IF(COUNTIF($A$6:$A$153,"*"&$F$6&"*")>=ROWS($F$6:$F6),SMALL(IF(ISNUMBER(SEARCH($F$6,$A$6:$A$153)),ROW($A$6:$A$153),""),ROWS($F$6:$F6))-5,""),1)),"",
INDEX($A$6:$A$153,IF(COUNTIF($A$6:$A$153,"*"&$F$6&"*")>=ROWS($F$6:$F6),SMALL(IF(ISNUMBER(SEARCH($F$6,$A$6:$A$153)),ROW($A$6:$A$153),""),ROWS($F$6:$F6))-5,""),1))

(pressed ctrl,shift,enter)

It worked! and I dragged the formula down so it would show all matches (like when typing max I would get in cell 1 maximiliano, the one below maxwell, etc). However, when I would leave the cell the formula used to do the search blank, it would fill in alphabetically (so in field it was blank ("") and in cell 1 I would get anna, then below brian, etc.).
Is there any way that I could make it so that when the search cell is blank, the results column will also be blank?

Just have that condition in the front of your fomula

=if([search cell]="","",IF(ISERROR(
INDEX($A$6:$A$153,IF(COUNTIF($A$6:$A$153,"*"&$F$6&"*")>=ROWS($F$6:$F6),SMALL(IF(ISNUMBER(SEARCH($F$6,$A$6:$A$153)),ROW($A$6:$A$153),""),ROWS($F$6:$F6))-5,""),1)),"",
INDEX($A$6:$A$153,IF(COUNTIF($A$6:$A$153,"*"&$F$6&"*")>=ROWS($F$6:$F6),SMALL(IF(ISNUMBER(SEARCH($F$6,$A$6:$A$153)),ROW($A$6:$A$153),""),ROWS($F$6:$F6))-5,""),1)))

 

[Mmeneses]

Just have that condition in the front of your fomula

=if([search cell]="","",IF(ISERROR(
INDEX($A$6:$A$153,IF(COUNTIF($A$6:$A$153,"*"&$F$6&"*")>=ROWS($F$6:$F6),SMALL(IF(ISNUMBER(SEARCH($F$6,$A$6:$A$153)),ROW($A$6:$A$153),""),ROWS($F$6:$F6))-5,""),1)),"",
INDEX($A$6:$A$153,IF(COUNTIF($A$6:$A$153,"*"&$F$6&"*")>=ROWS($F$6:$F6),SMALL(IF(ISNUMBER(SEARCH($F$6,$A$6:$A$153)),ROW($A$6:$A$153),""),ROWS($F$6:$F6))-5,""),1)))

Did you just realized what you did?!?!?!? You have absolutely made my day!!! thank you so much and hope you live happy forever!