VLOOKUP does not work with array formula
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 |
VLOOKUP does not work with array formula
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 |
All times are GMT +1. The time now is 07:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com