Finding the smallest/highest value within date range

Question

enter image description herehello I'm a beginner and don't know where to start. My problem is how to find the smallest value of a specific cell and the date adjacent to it considering multiple smallest value can pick any of the smallest value.

enter image description here

I'm using this value to find the date of the smallest value

=INDEX(B4:B41684,MATCH(MINIFS(D4:D41684,A4:A41684,F6,B4:B41684,">="&H2,B4:B41684,"<="&H3),D4:D41684,0))

while

(MINIFS(D4:D41684,A4:A41684,F6,B4:B41684,">="&H2,B4:B41684,"<="&H3)

to find the smallest.

At first with small arrays/sample the values I'm getting is right but when I used huge sample the results are wrong

If you're a beginner this is way beyond your pay grade. I'm not but it is beyond mine. However, I can tell you where to start. Start with researching formulas that combine the SMAll/LARGE functions with multiple criteria (3 in your case) matches. You may not find the solution but you will understand enough of the problem to encourage you to give up. At least, that's what happened to me. If you absolutely need this, consider a User Defined VBA Function (UDF) but be sure to try your hand at writing it yourself before you ask for it on a platter. — Variatus
to find the date adjacent to the smallest value I'm using =INDEX(B4:B41684,MATCH(MINIFS(D4:D41684,A4:A41684,F6,B4:B41684,">="&H2,B4:B41684,"<="&H3),D4:D41684,0)) but for larger arrays i get wrong values. For smaller arrays it is right — Redoneil Ragojo Reyes
Please provide a small sample of data that we can use to reproduce your problem. Edit your question to provide it as TEXT that can be copy/pasted into a worksheet. You can read Help for How to create a Minimal, Complete, and Verifiable example for hints how to do this. — Ron Rosenfeld

Solar Mike Solar Mike · Accepted Answer · 2020-11-04T06:58:19

I would use MINIFS() but that may depend on your version, see:

Finding the smallest/highest value within date range

1 Answers