# how to sum multiple columns in a vlookup or index match

#### bigdan

##### Well-known Member
I have an index match formula that I want to modify.

Here's the current formula:
=INDEX(\$C\$7:\$E\$12,MATCH(\$A19,\$A\$7:\$A\$12,0),2)

So this is looking at a range, C7-E12, and returning the relevant value in the column I specify (the last number in the formula, currently 2).

What I'd LIKE to do, is not just look at value in the current column, but all the columns prior to it. So when I type 2 I'd like it to sum the first columns (in the relevant row). If I type 4 I'd like to see the sum of 4 columns in that row.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Domenic

##### MrExcel MVP
Try...

=SUM(INDEX(\$C\$7:\$E\$12,MATCH(\$A19,\$A\$7:\$A\$12,0),1):INDEX(\$C\$7:\$E\$12,MATCH(\$A19,\$A\$7:\$A\$12,0),B19))

...where B19 contains the number of columns to sum.

Hope this helps!

Replies
5
Views
116
Replies
1
Views
197
Replies
7
Views
59
Replies
2
Views
38
Replies
3
Views
106