I am stumped and beaten and bow to greater persons.
Attached is worksheet in which I want to use to adjust and save the best overall Skyform ratings after scratchings. At present these are available in PDF form. This involves copying the data into excel. The base data is available via CSV files, but does not allow for scratchings. Neither allow for late scratching. From column HQ on (sheet 1) I have made a template which when you delete a TAB# in HQ, it automatically recalculates the values. I need to have column JA (B/O) as in some cases there are equal ranking in IQ. I have Column JB as I wish to still record the top 4 B/O in numerical order. Up to this point I am happy with the result, mainly because it is within my capabilities.

I have started transferring to sheet 2 using the formula =IFERROR(INDEX(Sheet1!$J$3:$J$22,MATCH(Sheet1!$JC$ 3,Sheet1!$JB$3:$JB$22,0),1),"")
This works but then I realized that it will not do exactly what I want. What I want to do is record the top 4 B/O in TAB# order, if these coincide with any of the top 4 Skyform rating, I want it recorded in column C sheet 2, otherwise N/A. In column D I want to record only the top 2 B/F ranking if they coincide with the top 4 B/O.
So I need to say =IF ( Sheet1!$J$3:$J$22<=4) then=IFERROR(INDEX(Sheet1!$J$3:$J$22,MATCH(Sheet1! $JC$3,Sheet1!$JB$3:$JB$22,0),1),"")
Only over the last 2 days trying to nut this out it will not work. I have gone to an alternative way
=IF(SUMPRODUCT((Sheet1!$HY$3:$HY$22<=4)*(Sheet1!JC3:$JC$22=A))=0,"",SUMPRODUCT((Sheet1!$HY$3:$HY$22<=4)*(Sheet1!$JB$3:$JB$22=A)*(Sheet1!$HY$3:$HY$22)) ) which only produces #Name?
Please help. My wall has a big hole in it and my head is sore.
Thanks beton

Attached Files

Mildura race mark formula.xlsx (54.0 KB, 591 views)