+ Reply to Thread
Results 1 to 6 of 6

HELP with ARRAY Formula

  1. #1
    Registered User
    Join Date
    02-02-2017
    Location
    Houston
    MS-Off Ver
    2010 and 2013
    Posts
    28

    HELP with ARRAY Formula

    Hi,
    I have an array formula using INDEX/MATCH to provide me the value when there are two criterias. (This formula is needed for column D on the 'StatTbl' tab)

    In the attached, I have finding the value of the 'Edit Date' when:

    New Value (column G) = Tech Review
    AND
    Case Number (column A) on the Raw Data tab = UniqueCase# on the StatTbl tab

    The formula to provide the above is:

    {=INDEX(RawData[Edit Date],MATCH(1,("Tech Review"=RawData[New Value])*([@[UniqueCase'#]]=RawData[Case Number]),0))}

    What I want to find is, the MAX date when the formula looks for the "Tech Review"=RawData[New Value].
    There are 2 situations that the New Value on the RawData tab has 2 dates for the same Case Number. (40738 & 53022).
    Right now the formula is generating the first date for those case numbers. I need the 'MAX' date.

    I cannot figure out the syntax to find this value.

    All help would be beneficial.
    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: HELP with ARRAY Formula

    StatTbl

    C2=IFERROR(IF(AGGREGATE(14,6,(RawData!$H$2:$H$2000)/(RawData!$G$2:$G$2000=StatTbl!C$1)*(RawData!$A$2:$A$2000=StatTbl!$A2),1)=0,"",AGGREGATE(14,6,(RawData!$H$2:$H$2000)/(RawData!$G$2:$G$2000=StatTbl!C$1)*(RawData!$A$2:$A$2000=StatTbl!$A2),1)),"")

    copy across and down

  3. #3
    Registered User
    Join Date
    02-02-2017
    Location
    Houston
    MS-Off Ver
    2010 and 2013
    Posts
    28

    Re: HELP with ARRAY Formula

    Thank you very much! This worked!

    I am familiar with the AGGREGATE function, but do not understand how the '/' works in the solution. What exactly does that do?

    thanks again!

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: HELP with ARRAY Formula


  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: HELP with ARRAY Formula

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  6. #6
    Registered User
    Join Date
    02-02-2017
    Location
    Houston
    MS-Off Ver
    2010 and 2013
    Posts
    28

+ 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. Array summing inventory of a specific part (array formula) EXCEL 2007
    By fdirosa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-07-2018, 12:51 PM
  2. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  3. Can I make the row lookup array/range part in an array formula variable?
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2014, 09:06 AM
  4. Array formula + Array formula with criteria that lookups a Table
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2013, 11:41 AM
  5. Replace hard coded array in formula with link to input array
    By David Brown in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2011, 07:45 AM
  6. Converting 3x10 array to a 1X30 array to run a Match formula
    By NBVC in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2009, 07:45 AM
  7. [SOLVED] Tricky array formula issue - Using array formula on one cell, then autofilling down a range
    By aspenbordr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2005, 11:05 AM

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