how to sum multiple columns in a vlookup or index match

bigdan

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

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
Joined
Mar 10, 2004
Messages
19,380
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,114,084
Messages
5,545,863
Members
410,711
Latest member
Josh324
Top