+ Reply to Thread
Results 1 to 8 of 8

How to avoid array formula ?

  1. #1
    Registered User
    Join Date
    02-29-2016
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    41

    How to avoid array formula ?

    Dear All,

    I created an array formula for multiple index match with "&" . Excel is taking long time to calculate. I want a simple formula instead of array formula

    =INDEX(C:C,MATCH(A2&B2,F:F&G:G,0))

    Required Quantity in H as per Code ( A) and Group (B)
    Attached Files Attached Files
    Last edited by siroopm; 03-14-2019 at 07:25 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: How to avoid array formula ?

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: How to avoid array formula ?

    a SUMIFS function is a good alternative in this case

    For cell H2 use formula and drag down to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-29-2016
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    41

    Re: How to avoid array formula ?

    Topic Edited
    Last edited by AliGW; 03-14-2019 at 08:13 AM.

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to avoid array formula ?

    Hi

    Try this formula in L2 and drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: Avoid the use of entire column.
    Last edited by José Augusto; 03-14-2019 at 08:26 AM. Reason: Moderator suggestion in post 8

  6. #6
    Registered User
    Join Date
    02-29-2016
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    41

    Re: How to avoid array formula ?

    it works.. thanks alot

  7. #7
    Registered User
    Join Date
    02-29-2016
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    41

    Re: How to avoid array formula ?

    Quote Originally Posted by José Augusto View Post
    Late answer.
    this also working..thanks alot.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: How to avoid array formula ?

    Quote Originally Posted by José Augusto View Post
    Late answer.
    Please do not remove alternative solutions - they may be helpful to others.

    Please post the solution you offered again. Thanks.

+ 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. [SOLVED] Avoid an Array Function by using Sumproduct
    By ibuhary in forum Excel General
    Replies: 8
    Last Post: 08-28-2018, 03:04 PM
  2. To avoid duplication of data extraction in array formula
    By ikm-99 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2016, 07:23 PM
  3. Replies: 10
    Last Post: 07-24-2016, 08:28 AM
  4. Help with Array Function: Avoid repeating values on MATCH/INDEX
    By jslater41 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-08-2014, 01:57 PM
  5. Array formula to avoid cell value repitition
    By himanshupd in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2013, 12:22 AM
  6. Array formula to avoid cell value repitition
    By himanshupd in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2013, 12:22 AM
  7. Is there a way to avoid using an array?
    By jram in forum Excel General
    Replies: 0
    Last Post: 04-26-2011, 09: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