Lookup in multiple sheets based on multiple variables

[Wolf Creek]

Hi All,

I am trying to put together a 'Trading Database' of a number of assets so that I can compare new developments by certain criteria. I have 25 assets in 25 separate tabs (and more will be added at a later date).

Each tab contains 10 years of historical and forecast trading data (2009 to 2018) and the assets are in a number of different markets, with a number of different quality ratings and a number of different locales. There are 19 cities, 8 states, 3 locales and 12 quality ratings (not sure if this bit is relevant). There is a cell in each sheet that concatenates that data into a 'Lookup Key' (i.e. CityStateLocaleQualityRating). Each Year column contains 33 rows of data per below.

Room Nights Available
Room Nights Occupied
Occupancy
ADR
RevPAR
Rooms
Food
Beverage
F&B Other
Total F&B	-
Telephone
Minor Other Departments
Rental & Other Income
Total Revenue	-
Rooms
F&B
Telephone
Minor Other Departments
Rental & Other Income
Total Cost of Sales	-
Administration & General
System Fees
Sales & Marketing
Energy
Repairs & Maintenance
Management Fee - Base
Management Fee - Incentive
Property Insurance
Property Rates & Tax
Lease Rent
Other Non-Operating Expenses
FF&E Reserve
Income to Owner	-

<colgroup><col><col></colgroup><tbody>
</tbody>

I have a 'Comparison Tool' tab that I want to enter in a specific lookup key and then the table to populate with an average of the data from all of the tabs that match that criteria.

So if only three of the assets match the search criteria then each of the 33 rows above will be populated with the average of the data in the relevant years.

I am aware that this is quite a confusing post, so please feel free to ask me for more information.

Regards.