Sum of VLookups in Numbers?

I may just be missing the right terminology, spreadsheets have never really been my thing. But I'm struggling to combine `SUM()` and `VLOOKUP()` for my needs. (Assuming these are even the functions I want to use, there could be something better that I'm just not noticing.)

Given a lookup table of Customers, for example:

``````Name  |  Miles from Office
--------------------------
Bob   |  3.4
Joe   |  9.1
Tom   |  2.9
``````

And a table of Transactions, for example:

``````Date     | Customer | Amount
----------------------------
1/1/2019 | Bob      | \$1.23
2/1/2019 | Tom      | \$2.34
3/1/2019 | Bob      | \$3.45
4/1/2019 | Bob      | \$4.56
5/1/2019 | Joe      | \$5.67
``````

How can I get a sum of "Miles" for the transactions? For any given transaction I could do:

``````=VLOOKUP("Bob", 'Customers'::A2:B4, 2)
``````

And that would successfully return `3.4`. But if I were to wrap that expression in a `SUM()` then how would I get it to run the `VLOOKUP()` on each item in the range being summed? I tried:

``````=SUM(VLOOKUP('Transactions'::B2:B6, 'Customers'::A2:B4, 2))
``````

Which I didn't think would work (but was worth a try), and indeed it didn't and just returned the first value, in this case `3.4`.

(Specifically using Numbers in iCloud in my browser, if that makes a difference.)

1 Answer

Create another column in Transactions. Put the VLOOKUP in that column. So then you have the miles for each one: 3.4, 2.9, 3.4, 3.4, 9.1. Then in another cell, use SUM(D2:D6) to add those up. If you don't want that column visible you can always hide it.