# sum of vlookup using array formula

I have such table of payments:

``````account | amount
--------+-------
101   | 3
101   | 5
102   | 7
103   | 9
``````

I named the range of this table "payments". Its first column I called "accounts". Its second column I called "amounts".

I also have another table which assigns accounts to groups:

``````account | group
--------+-------
101   | 1
102   | 1
103   | 2
``````

I named the range of this table "groups".

Now I want to sum all payments of accounts of group 1 using only one formula, without using any additional columns. I'm trying to do that using such array formula:

``````sum(if(vlookup(accounts,groups,2,false)=1,amounts,0))
``````

I also tried such array formula:

``````sumif(if(vlookup(accounts,groups,2,false)=1,amounts,0))
``````

These formulas do not work. I think I know why - it seems that `vlookup` function does not return array when used in one cell array formula.

So, how can I calculate it using one formula?

## migrated from stackoverflow.comFeb 26 '13 at 4:15

This question came from our site for professional and enthusiast programmers.

It seems to me that you can achieve what you are looking for using the following formula:

``````=SUMPRODUCT(B1:B4*(LOOKUP(A1:A4,C1:C3,D1:D3)=1))
``````

The `=1` refers to the group number of which you are looking for the accounts. Note that this is a regular formula, not an array formula.

I have used regular cell references as opposed to named regions, because I think that makes it easier to relate the formula to the XLS sheet.

The following screenshot shows where the different values are and includes a slightly more generic formula that you can use to do the same calculation for different groups by dragging the lower-right corner of cell `F1`.

If you want to stick to using names, you have to introduce to different names for the columns with accounts. the formula would look like this:

``````=SUMPRODUCT(Payments*(LOOKUP(Accounts1,Accounts2,Groups)=1))
``````

For the sake of completeness, check out How to use the LOOKUP function in Excel for the conditions under which you can use `LOOKUP`.

• Using LOOKUP is a valid approach, Reinier, but there are some possible downsides - firstly for `LOOKUP` to work correctly the "lookup range" [C1:C3] must be sorted ascending (as per example but I don't know if that can be guaranteed) and secondly if any of the accounts1 codes don't appear in the accounts2 list then you would either get an error or a "false match" - my approach doesn't have those potential drawbacks – barry houdini Jan 30 '13 at 9:50
• @barryhoudini For the problem statement, `LOOKUP` is the right function. And on the upside, don't you think the resulting formula is more elegant and better maintainable than, for example, yours ;-)? But from an educational standpoint, you are correct, I should have mentioned that. I extended the answer. – Reinier Torenbeek Jan 30 '13 at 17:36
• @user983447 Glad to help. Check out the link I added, or barry houdini's comment, for conditions under which this approach using `LOOKUP` works. – Reinier Torenbeek Jan 30 '13 at 17:42
• more elegant and better maintainable? Yes, definitely, good answer! Perhaps I was trying to cope with non-existent issues – barry houdini Jan 30 '13 at 17:43

Yes, you are correct, `VLOOKUP` won't return an array so you need another approach. Assume that first column of groups is called `accounts2` and second col is `numbers` then try this array formula,

``````=SUM(IF(ISNUMBER(MATCH(accounts,IF(numbers=1,accounts2),0)),amounts))
``````

confirm with CTRL+SHIFT+ENTER

• As far as I can see, it does not work. – user983447 Jan 30 '13 at 16:11
• did you use CTRL+SHIFT+ENTER? If you use that key combination you will get { and } around the formula and hopefully the correct result too – barry houdini Jan 30 '13 at 16:33