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.

[TABLE="width: 255"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Room Nights Available[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Room Nights Occupied[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Occupancy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ADR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RevPAR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rooms[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Food [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beverage[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F&B Other [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total F&B[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Telephone[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Minor Other Departments[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rental & Other Income[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Revenue[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Rooms[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]F&B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Telephone[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Minor Other Departments[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rental & Other Income[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Cost of Sales[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Administration & General[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]System Fees[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales & Marketing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Energy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Repairs & Maintenance[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Management Fee - Base[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Management Fee - Incentive[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Property Insurance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Property Rates & Tax[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lease Rent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other Non-Operating Expenses[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FF&E Reserve[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Income to Owner[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

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.