Combine a VLOOKUP and SUMIFS calculation into one cell

Question

Need some help streamlining calculations I have done in 2 parts into 1 cell.

Table 1 has Quarter, Name, Revenue eg.

1, John, 100
2, John, 200
2, Jane, 300
3, John, 400
3, John, 350

VLOOKUP Table2:

100 | 300   | 10
301 | 600   | 30
601 | BLANK | 50

What I've done originally is create a column next to the revenue (D) against the VLOOKUP to get the result in column 3 i.e = VLOOKUP (Table1C1, A1:C3,3,TRUE)

Then use SUMIFS against quarter and name to sum the values found in the VLOOKUP. How do I combine these together?

I've tried:

VLOOKUP(SUMIFS(Table1C:C,Table1A:A,1,Table1B:B,John),Table2A1:C3,3,TRUE)

This partially works but doesn't run the VLOOKUP against each revenue that fits the conditions before returning the assigned value and instead combines the revenue before referencing against the VLOOKUP.

I've created a dummy version if it helps https://docs.google.com/spreadsheets/d/1Hl-TLyfJowcnJU-h6mPgyJ5xUybtOaKb4xmyJozsJow/edit?usp=sharing

My attempt in yellow and combined VLOOKUP(SUMIFS()) in green which don't match.

Answer 1

Try this formula:

=SUMPRODUCT(LOOKUP($C$2:$C$14,Sheet2!$A$1:$C$3)*($B$2:$B$14="John")*($A$2:$A$14=1))

See image for reference

Note: Data in Column A of Sheet2 must be in ascending order. For details on LOOKUP see this.