Forum Discussion

Tom Sargeantson's avatar
Tom Sargeantson
Copper Contributor
Nov 22, 2017

Pass an array as an argument to lookup formula?

Hello,

 

I have a formula that returns an array of values, and what I'd like to do is expand the formula to take that array, perform a vlookup or index/match on each value in the array to return another array, and then sum that second array.

 

Is this possible?

 

I have tried just referencing the array as an argument in a vlookup but that just returns errors. I'm less familiar with index/match so I haven't fully explored that option yet.

 

Thanks,

Tom

Formulas & Functions
Need Help

5 Replies

Sort By
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Nov 24, 2017

         

        A few.

         

        {=SUM(VLOOKUP(T(IF({1},criteria_array)),lookup_range,column_number,FALSE))}
{=SUM(INDEX(output_column,N(IF(1,MATCH(criteria_array,lookup_column,0)))))}
=SUMPRODUCT(SUMIFS(output_column,lookup_column,criteria_array))
  • Tom Sargeantson's avatar
    Tom Sargeantson
    Copper Contributor
    Nov 22, 2017

    PS - I know I can sum an array simply using the sum() function, so this question is really about performing the lookup on an array, in an effort to return a second array.

Resources