Can someone help me with a formula I am struggling with in Excel? I want to look up a code in a table and then sum a range of values to the right of the selected code. In essence I am struggling to combine a sum formula with a vlookup formula. Thanks

Let's say your codes are in A1:A4, the data to Sum is in columns B:D and your search criteria (the code) will be in E1. Try this array formula:

=SUM(VLOOKUP(E1,A1:D4,{2,3,4},0))

entered using Ctrl-Shift-Enter to put the {} around the entire formula.

{=SUM(VLOOKUP(E1,A1:D4,{2,3,4},0))}

You can't just type the brackets to make it an array formula, you must use Ctrl-Shift-Enter each time you edit the formula.

Ask Your Question

Weekly Poll

Did you watch the X-Files when it was on TV?

Discuss in The Lounge

Poll History