VLOOKUP does not work with array formula

[axb]

I am using VLOOKUP as part of a SUM array formula like this:

{=SUM(VLOOKUP(A9:A10,COLORTABLE,2,FALSE))}

I was expecting that this would lookup the value associated with A9
and SUM it with the value associated with A10. That is my
understanding of how array formulas work.

This is not the case - instead, the value returned by the array SUM is
the single value associated with A9 and there is no array processing
going on.

Can someone confirm if VLOOKUP is not capable of working with array
formulas?

Thanks

 

[T. Valko]

Can someone confirm if VLOOKUP is not capable

of working with array formulas?

It won't work in an array in the manner you've tried.

It will work as a stand-alone when entered as a range array. For example,
select a 2 cell array and array enter:

=VLOOKUP(A9:A10,colortable,2,0)

To get your sum (normally entered):

=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX(colortable,,1),A9:A10,0))),INDEX(colortable,,2))