+ Reply to Thread
Results 1 to 6 of 6

Google Sheet Array formula

  1. #1
    Registered User
    Join Date
    07-29-2020
    Location
    Pakistan
    MS-Off Ver
    2016, 365
    Posts
    20

    Unhappy Google Sheet Array formula

    Using below formula to expand the result till last row using array but its just displaying single result where formula is being pasted. Please share the solution thanks in advance.



    =ARRAYFORMULA(IFERROR(INDEX('Stock In by Invoices'!$J$5:$J,MATCH(1,('Stock In by Invoices'!$E$5:$E=$D$5:$D)*('Stock In by Invoices'!$F$5:$F=$E$5:$E)*('Stock In by Invoices'!$I$5:$I=$I$5:$I),0))))

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Google Sheet Array formula

    i don't use google sheets, but have you seen this?

    https://support.google.com/docs/answer/3093275?hl=en

    that's the english version of the page. per the URL: hl=en

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

    Re: Google Sheet Array formula

    For my own simplicity, I'll refer to the worksheet in which you're entering this formula as This.

    Breaking down your formula, you want all items in 'Stock In by Invoices'!J5:J for which cells in 'Stock In by Invoices'!E5:E match This!D5:D AND 'Stock In by Invoices'!F5:F match This!E5:E AND 'Stock In by Invoices'!I5:I match This!I5:I. I figure Google Sheets' MATCH function will only return ONE value when its 1st argument is a single value.

    If you really are using Google Sheets, you should use its FILTER function for this.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-29-2020
    Location
    Pakistan
    MS-Off Ver
    2016, 365
    Posts
    20

    Re: Google Sheet Array formula

    hrlngrv

    Your formula is not displaying the result till last row.

  5. #5
    Registered User
    Join Date
    07-29-2020
    Location
    Pakistan
    MS-Off Ver
    2016, 365
    Posts
    20

    Re: Google Sheet Array formula

    Yes checked but could find the solution.

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

    Re: Google Sheet Array formula

    It works for me with very simple data. Since the problem domain is Google Sheets, here's a link to my test file on my Google Drive. Sheet1!A5:A shows the results of the filter formula for my data.

    Note the formulas in my Sheet1!B5:B. Perform the same test with your own data.

+ 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 Sheets (FILTER + ARRAY) Function translated to my EXCEL Sheet
    By dmcmaste in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-31-2020, 01:35 PM
  2. Google sheet: array formula help to input data matching other cells
    By rayted in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 08-21-2019, 08:49 AM
  3. Array Formula - Google Sheets -> Excel converter
    By dathgr in forum Excel General
    Replies: 4
    Last Post: 12-19-2018, 06:30 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. Google Sheets Array Formula Structure
    By redline24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2015, 04:00 PM
  6. Google Spreadsheets, how do I make an array formula?
    By puretppc in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 02-02-2014, 08:13 PM
  7. array formula google doc to excel
    By jettechfsr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-09-2013, 02:46 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1