Slight problem with Arrays Formula

**helpbitte** · 02-12-2013, 09:22 AM

Basically, I have 2 small tables (columns L-N) which show the results from a formula i was previously given and (columns P-R) which are the expected results that should have been returned. There is one error (highlighted red) and i have no idea why.

Any ideas?

Thanks

Adam

**Fotis1991** · 02-12-2013, 09:31 AM

=INDEX($G$4:$G$1000,MATCH(MIN(IF($F$4:$F$1000=L4,$D$4:$D$1000)),$D$4:$D$10000,0))

Array formula.

I saw your other thread earlier that ben offerrd you that solution. Your expected results for LAw in that thread were not correct. Did they?

**dilipandey** · 02-12-2013, 09:41 AM

Hi Adam,

You need to change the reference to min if you wish to obtain minimum values, see revised formula which is fetching 09:00 :-

=INDEX($G$4:$G$100000,MATCH(MIN(IF($F$4:$F$100000=$L4,$D$4:$D$100000))&$L4,$D$4:$D$100000&$F$4:$F$100000,0))

Regards,
DILIPandey
<click on below * if this helps>

**helpbitte** · 02-12-2013, 09:42 AM

Hi there,

thanks for that. Yes, I posted an earlier thread which worked, but after closer inspection....i detected a few errors.
Thanks for this new formula. It also looks great on first look, but actually it returns 5 correct results and 1 in-correct result (unless i am doing something wrong)
the incorrect result is the high for the 15/12/2010. The result with your formula gives me a time of 21:00, when the correct result should be 20:00

Do you get the same result, or have i dont something wrong?

thanks for your time and help.

Adam

**Fotis1991** · 02-12-2013, 09:54 AM

No Adam. We don't get the same result.

See your example sheet.

**benishiryo** · 02-12-2013, 10:14 AM

sorry about overlooking, Adam. Fotis' shorter version is a little risky if you have 2 different dates with the same High/Low. if you don't have such a scenario, then it'll be fine to use his shorter version.

attaching my adjusted formula, which Dilipandey has done.