+ Reply to Thread
Results 1 to 3 of 3

Google Sheets Array Query

  1. #1
    Registered User
    Join Date
    03-25-2021
    Location
    England
    MS-Off Ver
    None
    Posts
    2

    Question Google Sheets Array Query

    Hi All,

    I'm trying to get an array to function that will search for a selection of words within a column and if the word is found place the row number in the column, if it doesn't contain any of the words it is left blank.
    Obviously this can easily be achieved by dragging down an IF statement, but its 1000's of rows so it gets laggy!

    List of words are :

    Private
    Public
    Unlinked
    Linked
    Unavailable

    Data is in O2:O
    I want the array to be in P2:O

    I have figured out how to search for 1 word, but adding more words in, even when incapsulated in { } does not work.
    =ARRAYFORMULA(IF(ISERR(SEARCH("public",(O21:O)))=True,"",ROW(O21:O)))

    This array sort of works, however it spills the values in to adjacent columns which I don't want.
    =ArrayFormula(iferror(if(SEARCH({"private", "public", "linked", "unavailable"},O21:O),row(O21:O),""),""))

    Any assistance is much appreciated.

    Thanks in advance

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    714

    Re: Google Sheets Array Query

    Can you try this ?

    Data is assumed to be in O2:O as mentioned in your message...

    Please Login or Register  to view this content.
    Last edited by Haluk; 03-26-2021 at 04:08 AM.

  3. #3
    Registered User
    Join Date
    03-25-2021
    Location
    England
    MS-Off Ver
    None
    Posts
    2

    Re: Google Sheets Array Query

    Hi,
    Sadly that doesn't work.

    I think the issue is that in the other cells it has #VALUE!
    I presume it uses 1 col per search word rather than just returning when it matches

    eg :

    #VALUE! 21 #VALUE! #VALUE! #VALUE!
    #VALUE! #VALUE! 22 #VALUE! #VALUE!
    #VALUE! 23 #VALUE! #VALUE! #VALUE!
    #VALUE! #VALUE! 24 #VALUE! #VALUE!
    #VALUE! #VALUE! 25 #VALUE! #VALUE!
    #VALUE! #VALUE! #VALUE! #VALUE! 26
    27 #VALUE! #VALUE! #VALUE! #VALUE!
    #VALUE! #VALUE! #VALUE! 28 #VALUE!
    #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
    #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
    #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
    #VALUE! 32 #VALUE! #VALUE! #VALUE!
    #VALUE! #VALUE! 33 #VALUE! #VALUE!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Query in Google Sheets
    By PhilippBonnie in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 03-26-2021, 10:18 PM
  2. Google Sheets query function replacement
    By Ollie Frith in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-09-2019, 05:41 PM
  3. Google Sheets Array and importrange help
    By ckserra in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-04-2018, 01:59 PM
  4. [SOLVED] Converting Google Sheets QUERY to Correct Excel Formula
    By davis909 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-17-2017, 09:14 AM
  5. Google Sheets QUERY How can I do the same in Excel?
    By Ziggy21 in forum Excel General
    Replies: 12
    Last Post: 10-04-2015, 07:54 PM
  6. [SOLVED] Converting Google Sheets document with query to Excel
    By playmate in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-04-2015, 10:18 PM
  7. query w/ minimum requirement in google sheets
    By alansoftpublisher in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 12-14-2014, 04:59 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1