# 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?

Almost 7 years later, I bring a `VLOOKUP()` solution with a single array formula :)

https://stackoverflow.com/questions/47187863/can-excels-index-function-return-array/59311618#59311618

A formula with entire columns would require a `IFERROR(VLOOKUP();0)` construct in order to deal with the headings, otherwise the SUM will return a `#VALUE` error.

Excel screenshot

``````    A         B       C   D         E       F   G       H
+—————————+———————+———+—————————+———————+———+———————+————————————————
1 | account | group |   | account | group |   | group | total amount
|---------+-------+---+---------+-------+---+-------+----------------
2 | 101     | 1     |   | 101     | 1     |   | 1     | {FORMULA HERE}
3 | 102     | 1     |   | 102     | 1     |   |       |
4 | 103     | 2     |   | 103     | 2     |   |       |
``````

`{FORMULA HERE}`

en-us

``````=SUM(IF(IFERROR(VLOOKUP(N(IF({1},\$A:\$A)),\$D:\$E,2,0),0)=\$G\$2,\$B:\$B,0))
``````

fr-fr

``````=SOMME(SI(SIERREUR(RECHERCHEV(N(SI({1};\$A:\$A));\$D:\$E;2;0);0)=\$G\$2;\$B:\$B;0))
``````

Validate the array formula with CTRL + SHIFT + ENTER

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 Jan 30, 2013 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. Jan 30, 2013 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. Jan 30, 2013 at 17:42
• more elegant and better maintainable? Yes, definitely, good answer! Perhaps I was trying to cope with non-existent issues Jan 30, 2013 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. Jan 30, 2013 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 Jan 30, 2013 at 16:33