Nested Index or match formulas?
Attend Excelapalooza
Thanks Thanks:  0
Results 1 to 4 of 4

Thread: Nested Index or match formulas?

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Nested Index or match formulas?

     
    Hi Everyone, new member here.

    I have a particularly difficult challenge I have been unable to solve. Hopefully someone here can help.

    I have two seperate sets of information

    One is the source data and the other is my search criteria.
    Basically I need to match the earliest date(prior to any fluctuation in price) where the price matches the search criteria and the cost item matches the search criteria.
    I have added an example for cost item 3916 if you look at the data the earliest uninterrupted date where the prices match is May 2016.
    Any ideas?

    Source Data
    Cost item no Invoiced on Month unit price
    3916 August 2015 32.62
    3916 August 2015 32.62
    3916 August 2015 32.62
    3916 August 2015 32.62
    3916 August 2015 32.62
    3916 November 2015 28.54
    3916 November 2015 28.54
    3916 December 2015 28.54
    3916 December 2015 28.54
    3916 January 2016 28.54
    3916 February 2016 28.54
    3916 March 2016 28.54
    3916 March 2016 32.62
    3916 March 2016 32.62
    3916 April 2016 32.62
    3916 April 2016 32.62
    3916 May 2016 28.54
    3916 May 2016 28.54
    3916 May 2016 28.54
    Search Criteria
    Cost item No. QUANTITY_1_PRICE Retruned result
    46 0
    3839 150.6
    3847 25.3
    3849 25.3
    3855 29.26
    3856 29.26
    3864 29.26
    3866 89.69
    3870 29.26
    3878 45.24
    3882 45.24
    3886 35
    3893 55
    3894 24.24
    3900 18
    3907 0
    3911 0
    3916 28.54 May 2016
    3921 30
    Thanks in advance for any thoughts on this.

  2. #2
    Board Regular
    Join Date
    Nov 2015
    Location
    United States: Portland, OR
    Posts
    383
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested Index or match formulas?

    You can use an array formula:

    {=IFERROR(SMALL(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")}

    To enter an array formula place this formula in a cell then press control+shift+enter:
    =IFERROR(SMALL(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Nested Index or match formulas?

    Quote Originally Posted by Max1616 View Post
    You can use an array formula:

    {=IFERROR(SMALL(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")}

    To enter an array formula place this formula in a cell then press control+shift+enter:
    =IFERROR(SMALL(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")

    Thanks that is very close to what I am needing, except this formula is returning "November 2015" where as I need it to return the newest date where it first stopped matching the SourceCritera price.

  4. #4
    Board Regular
    Join Date
    Nov 2015
    Location
    United States: Portland, OR
    Posts
    383
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested Index or match formulas?

      
    Oh, I misread the original post and thought you wanted the oldest date. If you change the small formula to large it should work:

    {=IFERROR(LARGE(IF(SourceData!C:C=SourceCriteria!B2,IF(SourceData!A:A=SourceCriteria!A2,SourceData!B:B,"")),1),"")}
    Last edited by Max1616; Dec 8th, 2017 at 11:55 AM.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com