Hi, I have the following array formula:
{=IF($BK30=0,0,IF(ISERROR(MODE(IF('LASTPRICE!$C$1:$C$50000=$BF30,'LASTPRICE'!$G$1:$G$50000))),AVERAGE(IF('LASTPRICE'!$C$1:$C$50000=$BF30,'LASTPRICE'!$G$1:$G$50000)),MODE(IF('LASTPRICE'!$C$1:$C$50000=$BF30,'LASTPRICE'!$G$1:$G$50000))))}
What it does...