Hello

Through the help of this forum i have managed to develop the below formula which works a treat. But i now want to develop it further. In 90% of instances the information in Cell B31 only appears once on the spreadsheet it is going to but there are a few occasions it appears 4 or 5 times, i would like for those instances for it to return the average or minimum value as at the moment it returns the answer for the first row it finds a match.

=IF(HLOOKUP(C$6,INDIRECT("'"&E$4&" "&"Compliance"&"'!"&"$A$11:$AG$196"),MATCH(B31,INDIRECT("'"&E$4&" "&"Compliance"&"'!"&"$A$11:$A$196"),0),0)="","",HLOOKUP(C$6,INDIRECT("'"&E$4&" "&"Compliance"&"'!"&"$A$11:$AG$196"),MATCH(B31,INDIRECT("'"&E$4&" "&"Compliance"&"'!"&"$A$11:$A$196"),0),0))

Again any help received is much appreciated