There will be a way, though not VLOOKUP inside another VLOOKUP. VLOOKUP returns a single value.
Depending on if the data is sorted, maybe an OFFSET & COUNTIF could refer to the summation range. OFFSET is volatile. If you have many thousands of these it may not be too efficient. And working across worksheets is also not optimally efficient.
Have you considered a query table - you could do the hard work in SQL - very smart. And/or a pivot table?
Or SUMIF, maybe also using COUNTIF. Maybe use DSUM, etc.
Or even VBA coding.
There are probably many ways. Depends on your requirements.
Hope that gives you enough leads.
If you want more specific suggestions, please post details of your requirements.