+ Reply to Thread
Results 1 to 5 of 5

Switching from Google sheets, need help with arrayformulas or combining range in formula

  1. #1
    Registered User
    Join Date
    01-10-2019
    Location
    Latvia
    MS-Off Ver
    Excel 2016
    Posts
    2

    Switching from Google sheets, need help with arrayformulas or combining range in formula

    Hello,
    Because of work I need to start using Excel 2016

    One thing I was using a lot in Google docs was arrayformula. But I can not figure out how to use it in Excel (or with what formula I should substitute it)
    For example in Google sheets this formula randomly picks an item from one range:
    Please Login or Register  to view this content.
    With comments:
    Please Login or Register  to view this content.
    But I cant even make something like this {=SUM(G15:G24*H15:H24)} to work without using ctrl+shift+enter, If I try to type braces inside formula it gives me error

    Your help would be much appreciated!
    Juris
    Last edited by JurisBarro; 01-10-2019 at 08:34 PM. Reason: Accidental Enter hit

  2. #2
    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: Switching from Google sheets, need help with arrayformulas or combining range in formu

    Hi and welcome to the forum.

    It's not at all clear to me why you need anything more than a VLOOKUP. If you have numbers in A1:A17 and alongside on B1:B27 you have some other values that you want to pick on a random basis then

    Please Login or Register  to view this content.
    Note there's no point in using stuff like ROW(A1) or ROW(A27) since they of necessity evaluate to the numbers 1 & 27. If it's the actual VALUES in A1 & A27 that determine the lower and upper values then substitutes A1 & A27 for 1 & 27
    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.

  3. #3
    Registered User
    Join Date
    01-10-2019
    Location
    Latvia
    MS-Off Ver
    Excel 2016
    Posts
    2
    Quote Originally Posted by Richard Buttrey View Post
    Hi and welcome to the forum.

    It's not at all clear to me why you need anything more than a VLOOKUP. If you have numbers in A1:A17 and alongside on B1:B27 you have some other values that you want to pick on a random basis then

    Please Login or Register  to view this content.
    Note there's no point in using stuff like ROW(A1) or ROW(A27) since they of necessity evaluate to the numbers 1 & 27. If it's the actual VALUES in A1 & A27 that determine the lower and upper values then substitutes A1 & A27 for 1 & 27
    Thank You for Your answer!
    Yea, I totally agree about randbetween part. For some reasone I wrote it that way...
    Well the thing is I dont want to use (if its possible) an extra column for numbers. I just need to use A column where the Names are.
    But basically this is an example to find out the best way to work further. Maybe a better example would be, that I need to vlookup(C1, arrayformula({G1:G30,A1:A30}), 2) where:
    C1 is a number that appears in G column, and based on that I need to return cell from A column. If it would work like that, I could rearange columns just for this specific formula...
    Juris

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Switching from Google sheets, need help with arrayformulas or combining range in formu

    Juris

    Can you explain, in words, what the formulas are meant to do?
    If posting code please use code tags, see here.

  5. #5
    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: Switching from Google sheets, need help with arrayformulas or combining range in formu

    Quote Originally Posted by JurisBarro View Post
    Thank You for Your answer!
    Yea, I totally agree about randbetween part. For some reasone I wrote it that way...
    Well the thing is I dont want to use (if its possible) an extra column for numbers. I just need to use A column where the Names are.
    But basically this is an example to find out the best way to work further. Maybe a better example would be, that I need to vlookup(C1, arrayformula({G1:G30,A1:A30}), 2) where:
    C1 is a number that appears in G column, and based on that I need to return cell from A column. If it would work like that, I could rearange columns just for this specific formula...
    Juris
    In that case it sounds like you need a composite formula. An =MATCH() function to find the C1 value (which I'm assuming is the randome number - but you don't say) in column G and then the MATCH() function wrapped inside an INDEX() function to obtain the column A Value.

    But as Norie says, rather than us all guessing just upload the workbook with a note that clearly explains which numbers you are working with and what result you expect.

+ 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. Import Range Selecting Range [Google Sheets]
    By Fredbugatti in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 10-31-2018, 03:06 AM
  2. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  3. Find the nearest date in a range of dates formula (Google sheets)
    By kaytoc in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 10-20-2017, 05:20 PM
  4. Avoid Switching Sheets, Reference Sheet Without Switching Instead.
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2017, 06:59 PM
  5. Excel equivalent to Google Sheets' FILTER(range, condition)?
    By Trille in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-14-2016, 06:09 PM
  6. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM
  7. Basic Sum Formula in Google Sheets
    By lesoies in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 7
    Last Post: 08-27-2013, 04:05 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