Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Array: Counting multiple values within array | Excel Worksheet Functions | |||
vlookup with a sum of array | Excel Worksheet Functions | |||
VLOOKUP ARRAY | Excel Discussion (Misc queries) | |||
Use array to return array of values | Excel Worksheet Functions |