Home

How can I insert a search box into a spreadsheet that searches comma separated values in cells?

%3CLINGO-SUB%20id%3D%22lingo-sub-774357%22%20slang%3D%22en-US%22%3EHow%20can%20I%20insert%20a%20search%20box%20into%20a%20spreadsheet%20that%20searches%20comma%20separated%20values%20in%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774357%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20create%20a%20spreadsheet%20that%20allows%20the%20user%20to%20enter%20in%20a%20search%20term%20and%20return%20a%20listing%20of%20words%2Fterms%20based%20on%20keywords%20(comma%20separated)%20specified%20in%20a%20cell%20in%20the%20same%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20280px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124545i5A35284A65E1E5CE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excel%20example.JPG%22%20title%3D%22excel%20example.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-774357%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774371%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20insert%20a%20search%20box%20into%20a%20spreadsheet%20that%20searches%20comma%20separated%20values%20in%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774371%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382413%22%20target%3D%22_blank%22%3E%40jeremybritz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThe%20function%20used%20to%20search%20for%20a%20keyword%20is%20called%20%22Search%22%3CBR%20%2F%3EIf%20you%20type%20%22Apple%22%20as%20a%20keyword%20in%20Cell%20B1%20and%20you%20want%20to%20search%20in%20the%20Range%20A5%3AA50%20for%20the%20existence%20of%20this%20keyword%2C%3C%2FP%3E%3CP%3EYou%20can%20type%20IsNumber(Search(%24B%241%2CA1))%20%26gt%3B%26gt%3B%20and%20copy%20it%20all%20the%20way%20down%20%26gt%3B%26gt%3B%20it%20will%20return%20a%20TRUE%20if%20the%20keyword%20exists%20and%20a%20FALSE%20if%20the%20keyword%20does%20not%20exist%3C%2FP%3E%3CP%3EUsing%20this%20concept%20with%20an%20advanced%20filter%20enables%20you%20to%20extract%20the%20record%20having%20the%20keyword.%3C%2FP%3E%3CP%3EIf%20you%20have%20multiple%20keywords%20then%2C%20you%20need%20to%20include%20your%20function%20in%20an%20OR%20function%20and%20put%20the%20keywords%20in%20separate%20cells.%3C%2FP%3E%3CP%3EShould%20you%20wish%20a%20more%20customized%20answer%20then%20post%20an%20Excel%20spreadsheet%20with%20sample%20data.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable
7 Replies

@Deleted 

Hi

The function used to search for a keyword is called "Search"
If you type "Apple" as a keyword in Cell B1 and you want to search in the Range A5:A50 for the existence of this keyword,

You can type IsNumber(Search($B$1,A1)) >> and copy it all the way down >> it will return a TRUE if the keyword exists and a FALSE if the keyword does not exist

Using this concept with an advanced filter enables you to extract the record having the keyword.

If you have multiple keywords then, you need to include your function in an OR function and put the keywords in separate cells.

Should you wish a more customized answer then post an Excel spreadsheet with sample data.

Thanks

Nabil Mourad

@Deleted 

For such sample

image.png

you may use B1 to enter terms, in B2 add the formula

=IFERROR(INDEX($A$11:$A$14,AGGREGATE(15,6,1/ISNUMBER(SEARCH($B$1,$B$11:$B$14))*(ROW($B$11:$B$14)-ROW($B$10)),ROW()-ROW($B$1))),"")

and drag it down.

Please see attached.

@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.

@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"),
"")

@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.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies