Using Vlookup to lookup array, return array

[jcampbell]

Hi,

I am trying to use vlookup to lookup an array of inputs, and then return an array of outputs. Then, I want to average over these outputs. I'd like to do all of this in one function call. Is it possible to do this, or to do something similar to achieve the same result?

I am trying to do it, and it's not working.

This is what my formula says:

{=AVERAGE(VLOOKUP(B9:B10,B4:C6,2,0))}

cells B4:C6 contain the an array that looks like this:

a 1
b 2
c 3

Cells B9:B10 are as follows:

a
b

Thus, I want the vlookup to return the array {1,2}, average over those, and give 1.5. Instead, it returns 1.

My spreadsheet is enclosed. As you can see, B13 returns 1 instead of 1.5.

Thanks,
Jonathan