how to sum multiple columns in a vlookup or index match

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
782
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.


Please help!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,309
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,456
Messages
5,528,871
Members
409,842
Latest member
mfernandezcean
Top