Results 1 to 3 of 3

Thread: Index Match picking the smallest value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index Match picking the smallest value

    Hey all,

    I am cracking my head without finding a logical solution for this problem.

    This is a small picture of my database (on the left) and I created a pivot table (on the right) to summarize this information. I would like excel to get the lowest number from the column G (date) and insert it on the column A (Date created) from the pivot table, using the columns Project as reference.

    So, for example, for the project 0000016, excel should give 30/08/2019 as well as for the project 00000020, excel should give 01/08/2019.


    https://imggmi.com/full/2019/9/20/03...-full.jpg.html

    Can anyone please assist me with that?

    Thanks in advance!


  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,252
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Index Match picking the smallest value

    With the Pivot Table you could drag dates down to the VALUE field.
    Then click on Drop Down Arrow
    -Value field setting
    -Min

    With a formula you could use the array formula in I5 in the example below that must be entered with CTRL-SHIFT-ENTER. Then drag formula down.
    If you have a later ver. of Excel with the MINIFS function then the formula in cell J5.

    DEFGHIJ
    1
    2
    3ProjectDate Project
    4000188/30/2019 ProjectMin Date
    5000188/31/2019 000188/30/20198/30/2019
    6000188/31/2019 000208/1/20198/1/2019
    7000208/1/2019
    8000208/2/2019
    9000208/3/2019
    10000208/5/2019

    Spreadsheet Formulas
    CellFormula
    I5{=MIN(IF($D$4:$D$10=H5,$E$4:$E$10))}
    J5=MINIFS($E$4:$E$10,$D$4:$D$10,H5)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

  3. #3
    New Member
    Join Date
    Jul 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match picking the smallest value

    Man you're amazing. Thanks a lot for your help!

    I don't know why but I couldn't get the smallest value with the pivot table, but using (the second) spreadsheet formula worked perfectly. Cheers!

Some videos you may like

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
  •