The usual wisdom around pivot tables is that they are generated from a single source of data. That is generally true. However from Excel 2013 onwards you can use the data model feature. The same rules still apply as using a vlookup (or indeed index/match) in that you must have something in common between the two data sets but if you know your data is set up and good to go, using the data model might be an option.

In this tutorial we have two data sets (two tables in the same file) and we want to combine them to create a pivot table. This is the file I used – complete with instructions

See the video below..