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

1. ## 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?

2. ## 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.

 D E F G H I J 1 2 3 Project Date Project 4 00018 8/30/2019 Project Min Date 5 00018 8/31/2019 00018 8/30/2019 8/30/2019 6 00018 8/31/2019 00020 8/1/2019 8/1/2019 7 00020 8/1/2019 8 00020 8/2/2019 9 00020 8/3/2019 10 00020 8/5/2019

 Cell Formula 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. ## 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!

## User Tag List

#### Posting Permissions

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