Array Index formula to average 2 columns

[sgs31]

​This is what I have and it works on other cells that I don't want averaged.

=IFERROR(INDEX(Archive!$U$6:$U$10000, SMALL(IF($G$4=Archive!$B$6:$B$10000, ROW(Archive!$B$6:$B$10000)-ROW(Archive!$B$6)+1), ROW(1:1))),"")

For a few cells I want to average two different index locations. I tried...

=IFERROR(INDEX(AVERAGE(Archive!$U$6:$U$10000,Archive!$V$6:$V$10000), SMALL(IF($G$4=Archive!$B$6:$B$10000, ROW(Archive!$B$6:$B$10000)-ROW(Archive!$B$6)+1), ROW(1:1))),"")

Obviously this didn't work!

Is there a way to average this without making a new column for index with averages already in place (a lot more work)?

Any help would be greatly appreciated.

 

[taurean]

Shouldn't this:
=IFERROR(INDEX(AVERAGE...
be
=IFERROR(AVERAGE(INDEX...
like
=IFERROR(AVERAGE(INDEX1,INDEX2),"")
where Index1 returns first value...

 

[sgs31]

I was thinking the same thing... but I still can't get it to work. I must be writing it out wrong.

 

[sgs31]

=(INDEX(Archive!$U$6:$U$10000,SMALL(IF($G$4=Archive!$B$6:$B$10000,ROW(Archive!$B$6:$B$10000)-ROW(Archive!$B$6)+1),ROW(1:1)))+INDEX(Archive!$V$6:$V$10000,SMALL(IF($G$4=Archive!$B$6:$B$10000,ROW(Archive!$B$6:$B$10000)-ROW(Archive!$B$6)+1),ROW(1:1))))/2

couldn't figure it out so I just added and then divided... works good enough

 

[sgs31]

=IF(E7="","",(IFERROR((INDEX(Archive!$U$6:$U$10000,SMALL(IF($G$4=Archive!$B$6:$B$10000,ROW(Archive!$B$6:$B$10000)-ROW(Archive!$B$6)+1),ROW(1:1)))+INDEX(Archive!$V$6:$V$10000,SMALL(IF($G$4=Archive!$B$6:$B$10000,ROW(Archive!$B$6:$B$10000)-ROW(Archive!$B$6)+1),ROW(1:1)))),"")/2))

Cleaned up some errors for anyone else that may stumble upon it.