How do I add a range of numbers positioned relative to its criteria?

I'd like to sum up some named values by referring to their names in a separate sheet. Can I do this with a single formula in the second sheet?

Sheet 1:

``````|  A  | 3 |
|  B  | 5 |
|  C  | 2 |
|  D  | 4 |
``````

Sheet 2:

``````|  A  |
|  A  |
|  C  |
|  B  |
| =13 |
``````
-

That's possible.

Formula

Add the following formula in `Sheet2!A5`:

``````=SUM(ARRAYFORMULA(IFERROR(VLOOKUP(A1:A4;Sheet1!A:B;2*SIGN(ROW(A1:A4));0),"")))
``````

Explained

1. the `VLOOKUP` retrieves the corresponding value
2. the `SIGN` returns an array of the same length to that of the `VLOOKUP`, so that the `ARRAYFORMULA` works
3. the `IFERROR` returns nothing upon error
4. the `ARRAYFORMULA` will take on a range, rather than a cell
5. the `SUM` sums it up !!

Note

Make sure the ranges are fixed (`A1:A4`) or place the result of the `ARRAYFORMULA` somewhere else.

Example

I've prepared an example file for you and added a second column in sheet2, so that you can see what happens. Try replacing an `A` with an `E`. Example file: SUM VLOOKUP

-