+ Reply to Thread
Results 1 to 12 of 12

Converting index arrayformula from Google Sheets to Excel

  1. #1
    Registered User
    Join Date
    08-02-2018
    Location
    -
    MS-Off Ver
    -
    Posts
    5

    Converting index arrayformula from Google Sheets to Excel

    I'm trying to transition from Google Sheets to Office 365, and have some issues with converting certain formulas which seems to work differently.

    I have a table containing a list of transactions, as well as some columns with different cumulative calculations for different types of products. Now I am trying to create another table containing one row for each product, which lookup the latest row in the first table and pull information from it.

    In Google Sheets I have this working formula:
    =iferror(INDEX(arrayformula(filter(F:F,D:D<>"",row(D:D)=max(if(D:D=A3,row(D:D),0)))) ,1),0)

    Cell A3 contains the product name I want to lookup.
    Column D contains the product name in the transactions table.
    Column F contains the value I want to pull from that table.

    Any suggestions on how I can convert the formula above to Excel? (I tried to include a link to a shared Google Sheet with a small example, but I'm not allowed to include links it seems.)

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Converting index arrayformula from Google Sheets to Excel

    Hi, welcome for the forum

    I dont know Sheets much, so it makes translating that a bit hard.

    Could you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Converting index arrayformula from Google Sheets to Excel

    Hi, and welcome to the forum.

    You should be able to include a link - we get spammers all the time who do just that and are always having to remove them!

    But why not put your data into an Excel workbook and manually add the results you expect to see with explanations as to how you've arrived at the results. A representative sample would be sufficient.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    08-02-2018
    Location
    -
    MS-Off Ver
    -
    Posts
    5

    Re: Converting index arrayformula from Google Sheets to Excel

    Hopefully I managed to attach a small Excel-file with an example.

    Thanks for trying to help.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Converting index arrayformula from Google Sheets to Excel

    Richard, there is a 10-post min for adding links, to fight the spam, but the link needs to be in the post, not the title - still fighting to get that in

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Converting index arrayformula from Google Sheets to Excel

    Try this, based on your sample...
    =SUMIF($D$2:$D$8,A15,$E$2:$E$8)

  7. #7
    Registered User
    Join Date
    08-02-2018
    Location
    -
    MS-Off Ver
    -
    Posts
    5

    Re: Converting index arrayformula from Google Sheets to Excel

    That would work for the example I provided, but unfortunately some columns contain data that can't be summarized like that. I would need to look up values on the most recent row for each individual product.

    Sorry for attaching a too simplified example. The actual spreadsheet has quite a few columns so I thought it would be easier to create a small example from scratch.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Converting index arrayformula from Google Sheets to Excel

    I understand, it happens often. So can you provide a more realistic sample?

  9. #9
    Registered User
    Join Date
    08-02-2018
    Location
    -
    MS-Off Ver
    -
    Posts
    5

    Re: Converting index arrayformula from Google Sheets to Excel

    I can do that tomorrow (it's getting rather late here), but I'm not sure that would help much. You could imagine column G in my previous file containing a comment, i.e. a value that cannot be calculated in any way based on other cells.

    I'm trying to retrieve that comment only based on knowing the row number. For example, I know that the comment I am looking for is on row 4, so I want retreive the value in cell G4 (as the comments are located in column G).

    I can retrieve the latest/highest row number for a specific product by using this formula (it can be used in my example file), but I haven't managed to get it to work in combination with retreiving the value:
    {=MAX(IF(D:D=A2;ROW(D:D);0))}

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Converting index arrayformula from Google Sheets to Excel

    We really do need to see a specific example.
    For instance do you want both the value in column E plus the value in column G for the row in question?
    It's doubly confusing since you talk about a comment being in column G and then refer to it as a value !!!

  11. #11
    Registered User
    Join Date
    08-02-2018
    Location
    -
    MS-Off Ver
    -
    Posts
    5

    Re: Converting index arrayformula from Google Sheets to Excel

    It seems that explaining my issue and overcoming the language barrier was harder than I thought. I was under the impression that the term ‘value’ can refer to the content of a cell regardless of whether that is a number, text or some other type of data. I’ll try to use ‘content’ instead then to avoid any further confusion.

    As I have tried to explain I am trying to lookup the content of a specific cell, located on the most recent row for each individual product. I’m not sure why you’d think that the solution would be different depending on whether the content is a number, a word/sentence or some other type of data.

    Anyway, it can be solved by an array formula which combines MAX to select the most recent row for each product, ADDRESS to combine row and the relevant column, and finally INDIRECT to lookup the content of the cell defined in ADDRESS.

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Converting index arrayformula from Google Sheets to Excel

    "I was under the impression that the term ‘value’ can refer to the content of a cell regardless of whether that is a number, text or some other type of data."
    Precisely. That's why we like to understand whether you're talking about numbers or text/comments when you use the word 'value' generically and where it could mean either.

    However given the example you've uploaded and the results you display why not just use a Pivot Table. See attached.

    However it's still somewhat difficult to understand what you mean by a comment in column G and how this would affect what you want to see. You ask us to imagine what it would look like. Rather than asking us to imagine it would help a lot more if you were to show us.

    Your example is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy) as requested, and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.
    Attached Files Attached Files

+ 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 'Arrayformula' with nested 'Importrange' formula Excel Equivalent?
    By Tamarissa in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 07-10-2018, 04:13 PM
  2. Index/Match Help - Google Sheets
    By JenMasters84 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 04-25-2018, 02:52 PM
  3. [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
  4. Converting google sheets formula to excel
    By coldeskimo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-08-2016, 08:41 AM
  5. [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
  6. arrayformula from google doc to excel
    By shinkirou in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-04-2013, 11:51 AM
  7. Replies: 4
    Last Post: 08-05-2010, 01:09 PM

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