# Variable columns possible in Sumproduct (vlookup) formula

• I'm trying to use this formula:
=SUMPRODUCT(VLOOKUP(B\$4778,\$D\$4:\$DC\$4623,{4,5},0))

It works fine but I'd like to try to use a variable for the {4,5} portion of the formula (columns in the array to be summed) as the formula needs to change based on sheet inputs.

I have cells on the sheet that are to be used to set the initial and final columns to be searched (likely 10 columns, but the 10 columns would have to be selected from 90 some columns available).

I tried to use the Columns function but it didn't seem to work for me.

The columns are selected by users entering in cells the upper and lower limits of the search range and then I convert those values to the corresponding numerical column value.

So if they select 5 as lower and 10 as upper limit, I know I have to add 7 to get the correct data column on the data page (column 12) and likewise for upper (column 17).
The entire possible area to search is \$D\$4:\$DC\$4623. So, in the formula, if I wrote it out long ways: =SUMPRODUCT(VLOOKUP(B\$4778,\$D\$4:\$DC\$4623,{12,13,14,15,16,17},0)).
I'd prefer to write it out using variables, something like this: =SUMPRODUCT(VLOOKUP(B\$4778,\$D\$4:\$DC\$4623,{w:r},0)) where w would be 12 and r would be 17.

Thanks for any help!

also posted at: https://www.excelforum.com/exc…-formula.html#post4620770

• Re: Variable columns possible in Sumproduct (vlookup) formula

Hi,

With A1 and B1 containing your lower and upper bounds (e.g. 12 and 17) respectively:

ROW(INDEX(A:A,A1):INDEX(A:A,B1))

which is less volatile than (and therefore preferable to) the standard:

ROW(INDIRECT(A1&":"&B1))

Regards

• Re: Variable columns possible in Sumproduct (vlookup) formula

@ XOR LX,

Very elegant solution ... :wink:

A great fan of your site ... !!!

Cheers

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Variable columns possible in Sumproduct (vlookup) formula

Very kind of you to say so! :smile:

Cheers!

• Re: Variable columns possible in Sumproduct (vlookup) formula

Thanks all for the efforts!
I have a similar solution that is non-array:
=SUM(INDEX(\$D\$4:\$DC\$4623,MATCH(B\$4778,\$D\$4:\$D\$4623,0),D5INDEX(\$D\$4:\$DC\$4623,MATCH(B\$4778,\$D\$4:\$D\$4623,0),E5))
where d5 is lower age limit and e5 is upper age limit.

• Re: Variable columns possible in Sumproduct (vlookup) formula

Thanks for sharing your solution with the Forum ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!