+ Reply to Thread
Results 1 to 10 of 10

using aggregate with index giving num error ??

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,040

    using aggregate with index giving num error ??

    I have sheet Customer A and sheet breakdown, trying to extract from breakdown column J split, the first , the second , using aggregate formula with index based on column H of customer A but gettin Num error.

    Can anyone assist.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,438

    Re: using aggregate with index giving num error ??

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

  3. #3
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,040

    Re: using aggregate with index giving num error ??

    Thanks workings now.

    But Is there another method I d'ont want to use iferror infront of index as the sheet has big data.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,438

    Re: using aggregate with index giving num error ??

    What's wrong with using IfError?

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,499

    Re: using aggregate with index giving num error ??

    Quote Originally Posted by Fluff13 View Post
    What's wrong with using IfError?
    The OP has a point, if I understand correctly. See my explanation here, including suggested alternative.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,438

    Re: using aggregate with index giving num error ??

    @XOR LX
    I understand what you're saying, but I think that this is slightly different.
    My take is that some of the values in Col H of the "Customer A" sheet do not exist on the "Breakdown" sheet & therefore the OP wants to hide those errors.

  7. #7
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,040

    Re: using aggregate with index giving num error ??

    The only issue with iferror that I have to extract on large dataset of 300000 rows sometimes more .

    So I was asking if there is another technique instead of iferror.

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,438

    Re: using aggregate with index giving num error ??

    If you are simply trying to avoid the #NUM error, I'd use Iferror, however another option would be
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,040

    Re: using aggregate with index giving num error ??

    Thank you Fluff

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,438

    Re: using aggregate with index giving num error ??

    You're welcome & thanks for the feedback

+ 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] Create List - Index Match or Index Aggregate or other?
    By bambamclint in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-10-2019, 01:30 PM
  2. [SOLVED] INDEX-AGGREGATE with OR criteria
    By Glenn Kennedy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-16-2018, 09:32 AM
  3. Index Aggregate Troubleshoot
    By VirenS in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-16-2016, 07:32 AM
  4. Index, Aggregate, Row - Removing Duplicate W/ Index Help
    By adbexcel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-04-2016, 03:53 PM
  5. Replies: 5
    Last Post: 01-30-2015, 06:39 PM
  6. [SOLVED] Index, Match and Indirect giving REF error
    By stanleds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2013, 06:08 AM
  7. [SOLVED] Three questions starting with Index, Match giving #REF! error
    By JO505 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-02-2013, 07:42 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