Partial string search

[Aussiebear]

In the attached workbook, The Dodgy Bros (Zack & Jake) have the ability to find a Client Name based on a partial string search, thanks to a young backpacker who called himself Rory. Three days after putting young Rory on the bus to Darwin, Zack has had a change of mind and now wants the ability to also search by a partial IP Address. Since the current formula in cell F5 uses the Partial string in cell F2 to search the Table1 and returns the data to cells F5:I5 by using the following formula

=IF($F$2<>"",CHOOSECOLS(FILTER(Table1,ISNUMBER(SEARCH($F$2,Table1[Client Name]),{1,2,3,4},"")

How could Zack implement a partial search with the results also filling the cells F5:I5, or does it need to be another range?

[Paul_Hossler]

Try this. It seems to work






=IF($F$2<>"",
IF(ISNUMBER($F$2),
CHOOSECOLS(FILTER(Table1,ISNUMBER(SEARCH($F$2,Table1[IP_Address]))),{1,2,3,4}),
CHOOSECOLS(FILTER(Table1,ISNUMBER(SEARCH($F$2,Table1[Client_Name]))),{1,2,3,4})
)
)

[Aussiebear]

Thank you Paul. Is that an either or type formula?

[georgiboy]

You don't need to use CHOOSECOLS as the formula is already filtering the entire table, therefore, the entire table (all columns) will be returned in the result. If you were filtering a larger table (more columns) and wanted to return a select few, then CHOOSECOLS would be needed.

As for the filter, if you wanted to use F2 for either the partial name OR the partial IP the you would need to use the + symbol to join the two criteria within the FILTER function.


=FILTER(Table1,
(ISNUMBER(SEARCH($F$2,Table1[Client_Name])))+
(ISNUMBER(SEARCH($F$2,Table1[IP_Address])))
)

[Aussiebear]

Thank you georgiboy.