0

I'm working on creating a 'search function' for a google sheet. The idea is that someone can type in keywords they want and don't want and a list will auto-populate below. I'm trying to figure out how to make it case insensitive. I've seen that you can do (?i) but as I'm pinging off a cell, I'm not sure how to format that in (the things I've tried have lead to errors).

Posted my whole query below, but the bolded section is where I'm directly taking the search terms.

=iferror(query(filter('Archive Catalog-DO NOT TOUCH'!A2:W3247,if(ISBLANK('Archive Catalog-DO NOT TOUCH'!H2:H3247), "randomnessisathing",'Archive Catalog-DO NOT TOUCH'!H2:H3247)='Archive Catalog-DO NOT TOUCH'!H2:H3247, if(regexmatch('Archive Catalog-DO NOT TOUCH'!H2:H3247, (if(ISBLANK(D3),"randomnessisathing",D3))), 1, 0)=0, (if(regexmatch('Archive Catalog-DO NOT TOUCH'!H2:H3247, D2), 'Archive Catalog-DO NOT TOUCH'!H2:H3247, 0)='Archive Catalog-DO NOT TOUCH'!H2:H3247)),"select Col1"),"No results found.")

Thanks so much! Anna

Edit: Attaching a sample. Unfortunately, I can't force everything to be lower or Title case because I'm dealing with Titles that need to be exported as is elsewhere.

https://docs.google.com/spreadsheets/d/1AZs4tvrTvhvo9HqBJWt2_HwHJJLW6TidnHOBI58pDwA/edit?usp=sharing

Improve this question
4
  • if possible, it helps to provide a sample sheet, with sample data, for people to more clearly understand what you are trying to achieve, and what you have tried. I don't know if you've already tried to force everything to lower case prior to doing the comparison of your keywords against your data values. Commented Jul 8, 2020 at 17:35
  • Just did! @kirkg13 Commented Jul 8, 2020 at 21:26
  • Why not use "(?i)" & ..."? Commented Jul 11, 2020 at 18:43
  • @WiktorStribiżew Thank you! I just didn't know the formatting. Commented Jul 12, 2020 at 0:50

1 Answer 1

Reset to default
0

I may have a formula that works for you. Try this query:

=QUERY('Archive Catalog-DO NOT TOUCH'!H2:H,
   "SELECT H WHERE UPPER(H) CONTAINS '"& UPPER(D2) &"' 
           AND NOT UPPER(H) CONTAINS '"& UPPER(D3) &"' ",0)

This forces both sides of the comparison to uppercase, before doing the comparison. Then the query selects the records that contains the "includes" keyword, and doesn't contain the "excludes" keyword.

Note that if this works for you, but you want to enable multiple "includes" keywords, you can enter each keyword/string in its own cell, and use mutiple comparisons in the query to match all of the keywords.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.