Forum Discussion

4 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jul 31, 2019

    calof1 

    Given the Stock and Date in F3 and F4, respectively, the formula in F5 to return the next price is: 

    =LOOKUP(2,1/((A3:A7=F3)*(B3:B7>F4)*B3:B7=AGGREGATE(15,6,
    1/((A3:A7=F3)*(B3:B7>F4))*B3:B7,1)),
    C3:C7)

    For an unknown reason, I cannot attach the file despite several attempts to do so. There must have been a bug when the manner, by which files shall henceforth be attached, was modified. 

    • calof1's avatar
      calof1
      Iron Contributor
      Aug 01, 2019

      HiTwifoo 

       

      Thanks for the message.

       

      I was able to use the formula without the example, thank you for your time and assistance, it also solved my needs.

       

      Much appreciated.

  • Yury Tokarev's avatar
    Yury Tokarev
    Iron Contributor
    Jul 31, 2019

    Hi calof1,

     

    If you have Stock name selection in G2 and wish to list dates and prices for the selected product starting from range G3 for date and H3 for price, you can use the following formulas:

     

    G3:  =IFERROR(INDEX($B$3:$B$7,SMALL(IF($A$3:$A$7=$G$2,ROW($B$3:$B$7)-ROW($A$2)),ROW(A3)-ROW($A$2))),"")

    H3: =IFERROR(INDEX($C$3:$C$7,SMALL(IF($A$3:$A$7=$G$2,ROW($C$3:$C$7)-ROW($A$2)),ROW(A3)-ROW($A$2))),"")

     

    Both formulas must be array entered in each cell . If you then copy these formulas down, the next rows will show next date/price combination for the selected product.

     

    Please see the example attached

     

    Thanks

    Yury

     

    • calof1's avatar
      calof1
      Iron Contributor
      Aug 01, 2019

      HiYury Tokarev 

       

      Thanks for your help, this matches my needs perfectly.

       

      much appreciated.

       

      Kind regards,

Resources