+ Reply to Thread
Results 1 to 3 of 3

INDEX-AGGREGATE with OR criteria

  1. #1
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    INDEX-AGGREGATE with OR criteria

    Hi there folks. I'm on the other side of the Question-Answer axis for a change. This arose as a result of some random musings following my contribution to another thread here.

    Aim: INDEX-AGGREGATE to return multiple results, in a non-array formula, that works when the or criteria are either in columns or in rows.

    I know that that or criteria can be accommodated using ((criterion range =criterion 1)+(criterion range = criterion range 2)+....)

    Thats fine for 2 or three. But what if you wanted to have a variable number UP TO, say 10. Then that approach would be messy.

    If the OR criteria are in a row and are numbers (Cols J-L), and all 3 places are filled, no problem (column D). However, delete one of the 3 OR criteria and it falls over. E.g. delete 6 from L3. There is a "fix" in column E. It's immmune to the effect of deleting L3. It does not wotk if the OR criteria are text (column Q).


    Question 1. What "fix" to I need to apply to column R to provide the text equivalent of column E, while KEEPING the formula non-array?

    If the OR criteria are numbers and in a column, I need to use TRANSPOSE (column G). The "fix" still works (column H), but both column G & H are array formulae. That's a pity, since it's normally not needed with AGGREGATE.

    Question 2. Is there a non-array alternative to TRANSPOSE when the OR criteria are in COLUMNS?

    Finally,

    Question 3. Do the fixes still work in any non-array formulae when the OR criteria are in columns? If not, how can they be adapted to work?

    I'm sure that it's do-able... but I can't "see the wood for the trees" any more. Have fun...
    Attached Files Attached Files
    Glenn



  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,891

    Re: INDEX-AGGREGATE with OR criteria

    Use COUNTIF()

    in D3:

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$3:$A$14)/COUNTIF($J$3:$L$3,$A$3:$A$14),ROWS($1:1))),"")

    in G3:

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$3:$A$14)/COUNTIF($J$6:$J$8,$A$3:$A$14),ROWS($1:1))),"")

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: INDEX-AGGREGATE with OR criteria

    Cheers!! Brilliant job.

    You made it look so easy. You wouldn't believe the brain-pain I was getting!!

+ 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. INDEX and AGGREGATE to ignore 0's and functions
    By Allerdrengen in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-18-2018, 01:07 PM
  2. Index/Aggregate Function for Lookup
    By VirenS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2017, 12:09 PM
  3. Index Aggregate Troubleshoot
    By VirenS in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-16-2016, 07:32 AM
  4. Index/Aggregate Function for Lookup
    By VirenS in forum Excel General
    Replies: 0
    Last Post: 06-16-2016, 04:28 AM
  5. Convert INDEX, AGGREGATE to Macro
    By VirenS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2016, 02:08 PM
  6. 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
  7. INDIRECT, INDEX, AGGREGATE?? Help!
    By bbr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-26-2013, 08:03 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