+ Reply to Thread
Results 1 to 2 of 2

Excel formula is not working in Google Sheet # I need Help

  1. #1
    Registered User
    Join Date
    04-16-2021
    Location
    India
    MS-Off Ver
    Office 19
    Posts
    4

    Excel formula is not working in Google Sheet # I need Help

    I am generating attendance sheet in excel 19 in my computer. But when I upload it in Google Sheet then it not works for me. Fore more information please see the attachment.

    Thanks in Advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Excel formula is not working in Google Sheet # I need Help

    Since your problem is with Google Sheets, it would have been MUCH BETTER to have included the link https://drive.google.com/file/d/1ymu...ew?usp=sharing in your posting rather than require others to find it in your workbook. When it comes to Google Sheets, better to ignore the yellow box at the top of the page on this site and provide direct links to Google Sheets documents. Point: we need to load this in Google Sheets rather than Excel, and it's beyond a waste of time, bandwidth, local storage to have to download attached files from here, then upload them to our own Google Drives, open them there, and (best) save as Google Sheets.

    The problem is that Excel handles LOOKUP differently than Google Sheets does. That is, LOOKUP(2,1/(SIMPLE_array_expression),range) evaluates correctly in Excel without array formula entry, but needs to be ARRAYFORMULA(LOOKUP(2,1/(SIMPLE_array_expression),range)) or LOOKUP(2,INDEX(1/(SIMPLE_array_expression),0),range) in Google Sheets. By SIMPLE_array_expression I mean an array-valued expression not calling IF, TRANSPOSE, MMULT, or MINVERSE functions, all of which require array formula entry to work correctly. In other words, Excel has no problem processing the 2nd argument of LOOKUP(2,1/(one_range=x)/(another_range=y),yet_another_range) without array formula entry, but Google Sheets does. Wrapping the LOOKUP call (or the entire formula) inside ARRAYFORMULA or wrapping such array arguments to LOOKUP inside INDEX(...,0) are necessary in Google Sheets.

    That said, I'm not willing to bet that Google Sheet's LOOKUP function matches Excel's binary search semantics. That is, that LOOKUP(2,1/(some_range=x),another_range) would match 2 to the last (bottommost or rightmost) nonerror value in the boolean array and return the corresponding value from another_range. I figure it'd be FAR SAFER to use index(filter(another_range,some_range="x"),countif(some_range,"x")).

    I can sympathize with Google. MSFT has never documented this behavior from LOOKUP, definitely not in Excel Help, and if there's any mention of this in MSFT's OOXML ISO standard specs, I haven't been able to find it. Even if Excel's LOOKUP function has worked this way since Excel version 1 back in 1985, Google can't be faulted for refusing to mimic all Excel's undocumented features and semantics.

+ 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. Google sheet: Help to attachment file from Google Drive and send email fill in Google Form
    By sbv1986 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 03-01-2021, 10:47 PM
  2. [SOLVED] Google Sheets - SORT formula working but data not visible
    By Ian99099 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 11-30-2020, 06:57 PM
  3. Google sheet: copy and paste column in exact format not working
    By rayted in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 06-28-2020, 04:17 AM
  4. Array formula not working in Google Sheets
    By nappy1001 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 04-20-2018, 12:14 PM
  5. Formula for excel is not working in google docs
    By jphilipson in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-26-2016, 03:32 PM
  6. From Excel working to Google Sheets no longer working
    By rad1964 in forum Excel General
    Replies: 5
    Last Post: 07-19-2016, 10:23 PM
  7. [SOLVED] Importdata stopped working (google excel)
    By Ditch1983 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-11-2016, 04:37 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