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

Do you believe Microsoft when it says it views the "Desktop" as the core of Windows?

Discuss in The Lounge

Poll History