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

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))),I NDEX(colortable,,2))


--
Biff
Microsoft Excel MVP


"axb" wrote in message
...
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