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.)
Best 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.