Lookups Using Multiple Arrays

In the example that I've attached, I'm trying to look up prices for my "Final Data Set" from two different arrays of data, "Array 1" and "Array 2".

You'll notice that there are some repeating part #'s with different prices in the 2 arrays.

My question: How can I insert either an average or highest value for a given part # into the "Final Data Set" from the Arrays the simplest way?


BONUS: In my data set there already are some prices filled in (for example in cell C6 there'd be the number 30). How do I average this in with the newly "lookeduped" data or simple not have it overwritten when I copy the formula down the column. NOTE: the real data set is huge - 10's thousands of rows.

If your arrays are actually listed side by side like your sample (or you can get them that way), then to get Average try:

=SUMIF($G$2:$L$8,B2,$H$2:$M$8)/COUNTIF($G$2:$L$8,B2)

or XL2007, perhaps

=AVERAGEIF($G$2:$L$8,B2,$H$2:$M$8)


copied down

and to get Minimum try:

=MIN(IF($G$2:$L$8=B2,$H$2:$M$8))

This formula needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER so that { } brackets appear around it.