Vlookup on an array of values
 

I have a list of names in rows and another sheet that has a table with
corresponding values to the above names ("AugGrid"). What I want to
do essentially is a product of the values associated with those names.

I'm trying a couple of things, and none of them are working.

First I tried: (as an array formula)

=PRODUCT(IF(AJ2:ALBI2 < 0, VLOOKUP(AJ2:BI2,AugGrid,5), 1))

where AJ2:BI2 are the names that are found in AugGrid

But when I step through this, the AJ2:BI2 in VLOOKUP is getting
evaluated as the first value in that series and not the whole series.
I also tried putting the other arguments to VLOOKUP as arrays, but it
wouldn't take an array as the second argument.

I also tried:

=PRODUCT(IF(AJ2:BI2 < 0, INDEX(AugGrid, MATCH(AJ2:BI2, AugStatNames,
0), 5), 1))

Where AugStatNames are the names in AugGrid that I'm looking up, but
in this case, too, the parameter to MATCH is evaluating as the first
in the series rather than the whole series.

Is this even possible to do as a single formula?

It is easy to do with *lots* of typing plus multiple cells for sub-
results, since all the typing won't fit in once cell, or extra cells
for sub-results, but all that is messy and I would like to figure out
how to make something nicer.

Thanks.

Vlookup on an array of values
 

Try:

=PRODUCT(INDEX(AugGrid,0,5)^COUNTIF(AJ2:BI2,AugSta tNames))

array entered.


"Bedevere13" wrote:

I have a list of names in rows and another sheet that has a table with
corresponding values to the above names ("AugGrid"). What I want to
do essentially is a product of the values associated with those names.

I'm trying a couple of things, and none of them are working.

First I tried: (as an array formula)

=PRODUCT(IF(AJ2:ALBI2 < 0, VLOOKUP(AJ2:BI2,AugGrid,5), 1))

where AJ2:BI2 are the names that are found in AugGrid

But when I step through this, the AJ2:BI2 in VLOOKUP is getting
evaluated as the first value in that series and not the whole series.
I also tried putting the other arguments to VLOOKUP as arrays, but it
wouldn't take an array as the second argument.

I also tried:

=PRODUCT(IF(AJ2:BI2 < 0, INDEX(AugGrid, MATCH(AJ2:BI2, AugStatNames,
0), 5), 1))

Where AugStatNames are the names in AugGrid that I'm looking up, but
in this case, too, the parameter to MATCH is evaluating as the first
in the series rather than the whole series.

Is this even possible to do as a single formula?

It is easy to do with *lots* of typing plus multiple cells for sub-
results, since all the typing won't fit in once cell, or extra cells
for sub-results, but all that is messy and I would like to figure out
how to make something nicer.

Thanks.