Multiple Criteria formula - HELP DESPERATELY NEEDED!

**JJMC1981** · 05-17-2013, 10:42 AM

Hello,

Hope you are all well. I've been racking my brains on how to solve this problem and I sadly my brain simply isn't up to the challenge. I run a small travel company and I want to set up a "hotel price calculator" on excel. On the back tabs I have a list of months (may, june, july etc), a list of hotels and a list of room grade (standard, suite etc) and finally a price for every single potential outcome.

Ultimately I want to be able to select a Month, followed by a hotel, followed by room grade and then for the price recorded on the back tabs for that criteria, to automatically appear.

Any help would be hugely appreciated.

Many thanks,

James

**judgeh59** · 05-17-2013, 10:44 AM

Hello James and welcome to the Forum....could you upload a data set with all of the company confidential stuff removed....thanks

**JJMC1981** · 05-17-2013, 10:58 AM

Front Sheet.jpg Back Sheet.jpg

As you'll see from the back sheet I was playing around with having a with how I presented the back sheet (see May vs June), happy to be flexible to what the relevant formula requires.

Let me know your thoughts.

**judgeh59** · 05-17-2013, 11:09 AM

Thanks James.....I was sort of hoping you could upload the excel file so I don't have to type a bunch of stuff in, in the process of creating the formulas....thanks

**JJMC1981** · 05-17-2013, 11:16 AM

Sorry, I figured as much but I'm new to this and I couldn't find the icon that would let me attached files - only images! You help is much appreciated.

**Tony Valko** · 05-17-2013, 11:17 AM

See if you can adapt this to your application.

Let's assume this is your data in the range A2:D5...

A...B...C...10
D...E...F...14
G...H...I...11
J...K...L...32

You want to return the price for the combination of D, E and F.

Use cells to hold the criteria:

A10 = D
B10 = E
C10 = F

Enter this array formula** in D10:

=INDEX(D2:D5,MATCH(1,IF(A2:A5=A10,IF(B2:B5=B10,IF(C2:C5=C10,1))),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

DaveDeV · 05-17-2013, 12:01 PM

Hi James,

This application would be better handled in a database application than a spreadsheet for one major reason - you need to be able to book more than 12 months ahead so your "Month" will also need to include a "Year" component to allow Excel to differentiate between this May in 2013 and next May in 2014 when the rates may well be different.

Also, your "Hotel Locator" needs to also allow for "Country" and "City/Town/Suburb" parameters to handle hotel chains with more than one hotel per country and city.

The number of potential hotels (including permutations of Chain, Country, City, Suburb) will make selection via drop-down selection less than attractive.

The only solution I can come up with is to construct a table with a "key" comprising a concatenation of all permutation identifiers including room grade and populate additional 12 columns with the rates for the 12 months. Using drop-downs, select each component of the key and use the month number to handle the LOOKUP offset to get the right rate for the month... This not ideal as the key construction could result in some complexity in the sub-tables to avoid constructing an invalid permutation (eg Southern Sun Hotel / Patagonia / Helsinki / Hyde Park)

Rather you than me...

Dave

DaveDeV · 05-17-2013, 12:11 PM

The sub-tables mentioned allude to the need for a set of hierarchical tables that allow for "structures" whereby:

a) A Country Table would have a sub-table containing all potential locations in that country and nowhere else
b) The location sub-table would have a lower level of sub-table containing all potential hotels in that location
c) A hotel sub-sub-table would need a further level of sub-table for room grades available and rates by month by year

This implies a data taxonomy that would resemble one of those hen & chickens plants...

Complex to build and a pain in the rear to maintain

Dave