Create a list of Min values from data table

As the title suggests I want to create a list of Lap records from a list of races, listed by year and venue.

The table Race Data is a list of the raw data that I want to pare down to a list of records for each venue. The times are listed in 2 columns "N & O". One is the time for qualifying and the other is a list of times during the race. The fastest time for a venue of either qualifying or race time is the record. I can figure out how to find the Fastest time (lowest value) for one column but trying to select between 2 columns is giving me a problem.

I also want to match the fastest time with a Driver (column I) and the year (column C).

Thanks

Jim O

This ARRAY formula gives you the expected result?

=MIN(IF('Race Data'!$E$2:$E$100=C2,'Race Data'!$N$2:$O$100))

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

Does this works?

I think this is the correct one..

=IFERROR(IFERROR(INDEX('Race Data'!$I$2:$I$97;MATCH(D2;'Race Data'!$N$2:$N$97;0));INDEX('Race Data'!$I$2:$I$97;MATCH(D2;'Race Data'!$O$2:$O$97;0)));"no results")


Edit: @ XOR LX

Now i think the same. Just way i changed my previous post formulas.

And this array formula in F2 will get you your Driver (can't believe there's nothing simper, though!):

=INDEX('Race Data'!$I$2:$I$97,CHOOSE(1+(MIN(IF('Race Data'!$E$2:$E$97=Record!$C2,'Race Data'!$N$2:$N$97))<MIN(IF('Race Data'!$E$2:$E$97=Record!$C2,'Race Data'!$O$2:$O$97))),MATCH(MIN(IF('Race Data'!$E$2:$E$97=Record!$C2,'Race Data'!$O$2:$O$97)),'Race Data'!$O$2:$O$97,0),MATCH(MIN(IF('Race Data'!$E$2:$E$97=Record!$C2,'Race Data'!$N$2:$N$97)),'Race Data'!$N$2:$N$97,0)))

Change this part at the start: 'Race Data'!$I$2:$I$97 to 'Race Data'!$C$2:$C$97 and keep everything else the same for the Year.

Notice that there are a few #N/As in there due to Indianapolis Motor Speedway having blanks in the Race time column.

Regards

I thought the idea was that the fastest time could come from either column N or column O?

Which is what my (rather cumbersome) formula attempted to resolve.

Regards

Jim.A quick view to sheet(i don't have time any more..) looks that for Indianapolis there are not times for the race. Only for pole...Look around it.

@Fotis

Not sure I agree with your logic there. How do you know that a given time doesn't match in the first choice of indexed columns, but for a completely different race/driver?

@JimO

I will try and have a look at adapting my formula for cases where blanks are present, though I haven't yet received any feedback so not even sure that the first version's giving you the desired results?

Regards

Originally Posted by XOR LX

@Fotis

Not sure I agree with your logic there. How do you know that a given time doesn't match in the first choice of indexed columns, but for a completely different race/driver?

Me too! It was Friday evening(21:00pm) when i posted my last reply...

So my opinion is that these INDEX & MATCH formula don't give the correct result because they try to match only the faster time and not the venue AND the faster time!! This ARRAY entered formula does this.

=IFERROR(INDEX('Race Data'!$I$2:$I$100;MATCH($C2&$D2;'Race Data'!$E$2:$E$100&'Race Data'!$N$2:$N$100;0));INDEX('Race Data'!$I$2:$I$100;MATCH($C2&$D2;'Race Data'!$E$2:$E$100&'Race Data'!$O$2:$O$100;0)))

This formula gives almost same results as the first one EXCEPT of 1 result which proove that first formula was not correct. Look the results for AVUS venue and compare the results..

..As their are only a few Races at Indianapolis I just add the pole time to the race time, so their are no blank values. I can make any modification later.

@ Jim

Min(if formula takes the empty cells like zeros values and gives 0 as result. I modified a little the formula(adding 1 more if) and now works fine!

=MIN(IF('Race Data'!$E$2:$E$1000=C2;IF('Race Data'!$N$2:$O$1000<>"";'Race Data'!$N$2:$O$1000)))