Forum Discussion
How can I insert a search box into a spreadsheet that searches comma separated values in cells?
......
Deleted ,
The workaround could be to wrap keywords by spaces and search for " apple ", not "apple", like
=IFERROR(
INDEX($A$11:$A$14,
AGGREGATE(15,6,
1/ISNUMBER(SEARCH(" " & $B$1 & " "," " & SUBSTITUTE($B$11:$B$14,","," ")))*
(ROW($B$11:$B$14)-ROW($B$10)),
ROW()-ROW($B$1)
)
),
"")
If there are spaces within your keywords you may use any other character instead of space. Please check attached.
- DeletedJul 31, 2019
......
- SergeiBaklanJul 31, 2019Diamond Contributor
Deleted
This new editor doesn't allow to attach the file, it's the same as previous time, only TEXT(...) is added to the formula.
- SergeiBaklanJul 31, 2019Diamond Contributor
Deleted
Okay, I'll try.
Let start from wrapping - by SUBSTITUTE the comma on space and adding the leading space we are sure what each keyword in the string is starts and end with space, e.g we have " table chairs " instead of "table, chairs". Adding spaces to lookup value we ensure what SEARCH find only full keywords. Search of " tab " returns an error, only " tables " gives some result. Without that we find with correct result "tab" in "table, chairs".
ISNUMBER returns TRUE if SEARCH found the keyword and FALSE otherwise. In arithmetic operations TRUE and FALSE are equivalent to 1 and 0.
With AGGREGATE we find nth (last parameter, ROW()-ROW($B$1)) smallest (first parameter 15) value in the array ignoring all errors (second parameter 6).
Our array here 1/ISNUMBER(...) as before which returns an error (1/0) or 1 (1/1) depends on found keyword in records of the range $B$11:$B$14 or not; and multiplied on sequential number of row in this range. If keyword in first and fourth category record, resulting array will be like {1,error,error,4}. For the first cell AGGREGATE returns first smallest value (1), for the next cell second smallest value ignoring errors (4), and for the third cell an error will be returned since we have only two smallest values. Actually they are positions of records in Category keywords list.
INDEX takes these positions and returns Category names from $A$11:$A$14.
Finally IFERROR returns empty string if now keyword found.
To add current date you may add it to the value returned by INDEX like
=IFERROR( INDEX($A$11:$A$14, AGGREGATE(15,6, 1/ISNUMBER(SEARCH(" " & $B$1 & " "," " & SUBSTITUTE($B$11:$B$14,","," ")))* (ROW($B$11:$B$14)-ROW($B$10)), ROW()-ROW($B$1) ) ) & "-" & TEXT(TODAY(),"yyyymmdd"), "")