+ Reply to Thread
Results 1 to 4 of 4

Converting Google Sheets QUERY to Correct Excel Formula

  1. #1
    Registered User
    Join Date
    12-12-2016
    Location
    Tulsa, Oklahoma
    MS-Off Ver
    2016
    Posts
    5

    Converting Google Sheets QUERY to Correct Excel Formula

    Hi all, been driven crazy all week trying to work a formula out and could use some help. Basically I'm looking to get the equivalent of a VLOOKUP that returns all occurrences of a given single criteria from a given range. My problem is that I need to be able to use a wildcard, so doing INDEX alone is no good and I haven't been able to work something out using MATCH. However, I was playing around in Google Sheets and I was able to get EXACTLY what I needed, but naturally it wasn't able to properly convert when saving the sheet as an Excel file.

    What I'm trying to put together is list of addresses and related data on one sheet, and then to be able to search for the street address on another sheet. Each address is going to be listed three or four times with different related data, and I need to be able to have results come up as I search for partials, because street addresses can naturally be entered incorrectly, misspellings, alternate versions, etc. I included the LOWER fuction as a kind of workaround because I need the search to be case insensitive.

    Here is the link to the sheet and the QUERY

    Please Login or Register  to view this content.
    Google Sheet - The QUERY lives in cell cell B9. If you type a partial address in D6 and press enter, it searches the range in Data! and returns the results.

    Thanks in advance for any suggestions!
    Last edited by davis909; 04-17-2017 at 09:02 AM.

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    any
    Posts
    8,750

    Re: Converting Google Sheets QUERY to Correct Excel Formula

    With your example, you can try this one:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    00repsolv.gif
    see att.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-12-2016
    Location
    Tulsa, Oklahoma
    MS-Off Ver
    2016
    Posts
    5

    Re: Converting Google Sheets QUERY to Correct Excel Formula

    Quote Originally Posted by sandy666 View Post
    With your example, you can try this one:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Attachment 513096
    see att.
    That did it, thank you so much!

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    any
    Posts
    8,750

    Re: Converting Google Sheets QUERY to Correct Excel Formula

    You are welcome and thanks for rep

+ 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. Converting google sheets formula to excel
    By coldeskimo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-08-2016, 08:41 AM
  2. 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
  3. [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
  4. Google Sheets Query IF column problem Converted to Excel Function?
    By Garthilk in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-24-2015, 10:37 PM
  5. Grab next same value in Excel/Google Sheets for RANK/MATCH formula
    By jpsear in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2015, 07:52 PM
  6. 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
  7. Replies: 4
    Last Post: 08-05-2010, 01:09 PM

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06414 seconds
  • Memory Usage 8,989KB
  • Queries Executed 16 (?)
More Information
Template Usage (36):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (4)postbit_legacy
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (50):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1