• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup but based on various criteria

W

WO

New Member
Hi Awesome folks!

Can anyone assist please?

Im attaching a spreadsheet with a couple of tabs. The cells highlighted in red are what im needing to be 'looked up'

Basically im wanting this particular formula to look at 'Quote sheet adult' D43 and see it says Zanzibar Serena Inn and D44 has a date. I need a formula to then find Zanzibar Serena Inn on the tab ZNZ Serena, find the corresponding date to D44 and then input the 'rate' in this case 150.00 in cell D46.

Im not too hot on lookups/If so any help much appreciated.

My next post after would be something like - ok, but if the date i chose was 31 July and had 7 nights this goes over two date/price bands. Can excel work this out?

really appreciate any help - Chandoo - i have joined excel school by the way, i just havent started it yet!!
 
Sorry i didnt link the file!

Does this work?

https://skydrive.live.com/#cid=134116E7ED504EE7&id=134116E7ED504EE7%21115

Thanks
 
Hi, WO!


The link worked fine.


The D46 cell formula might be:

=BUSCARH(D44;'ZNZ Serena'!B7:M8;2;VERDADERO) -----> in english: =HLOOKUP(D44,'ZNZ Serena'!B7:M8,2,TRUE)


Regards!
 
Hey thanks guys.

the Index option seems to work for finding the right rate for the date. If i had more than one hotel in the D column could we then look up to find the matching hotel if i have more than one set of rates?

Andrew
 
Your data inst really set up to run lookup functions. It was be easy as hell if you had a data page where all your data was in a grid format. Then you could use lookup functions on another sheet, in this case, the sheet your using now. This would be your presenter worksheet
 
I can possibly create a 'grid data' page, even though im not totally sure what one is!!

Its basically date bands and hotel rates against individual hotels, so im thinking the data is pretty rigid so should be able to fit into some sort of grid!?

I know this is stupid but what would be an example 'grid' Montrey?

Thanks again, really apprecizte this lesson!

Andrew
 
For instance. Row 1 would contain headers name of hotel, then all dates

row 2 and below would contain your data. Name of hotel, then all rates for those dates.
 
Hi, WO!


I yet thought the same as Montrey posted, but I was waiting till your next question to see if could be accomplished without having to rebuild from scratch your data grid.


Additionally to that about no spaces I'd strongly suggest entering dates as "dates from" or "dates to" and not as "strings from/to".


I believe that you must build a new sheet with all the data well organized, for which a first approach might be:

Hotel

Room type / Meal basis

Up to date

Nett Curr

Nett GBP

Gross GBP


I hope I didn't miss any parameter, if so, please add it to the data structure before constructing the grid. Just advise if any trouble.


Regards!
 
Thanks again folks. Still not sure ive got it right but ive uploaded. Maybe you'd be so kind as to have a look? I still have a problem how to enter multiple dates into my grid - cant get a grip on how it should look!

Much appreciated

Andrew

Should be back on skyDrive as above - Quote Sheet 1
 
Hi, WO!


I was giving a look to your new ZNZ Serena worksheet and I think that there's very basic information. I don't know how you fill your quotes but I see lot of data in them that are not reflected in your data table.


Let's write down some points:

- if the worksheet is named ZNZ Serena, you should have data only for that hotel (which might be complex if you have lots of countries/places with lots of hotels, so I recommend you to make only one data sheet for all figures: add Country/City(Place) as columns before Hotel

- beachfront isn't an attribute for Double and other room types?: don't mix attributes, one column for room type, another for pax#, another for beach front, cottage, whatsoever

- date band is a date value, not a month: format column as dd/mm/yyyy and enter proper data

- where are prices for more number of nights or long stays? another column


Just examples with the intention to lead you build a data sheet with ALL the available info & figures for every case... Once you have that, then passing the correct parameters to your query function (country, hotel, pax#, room typ, meal basis, date, number of nigths/longstay, date) will make it easy and exact to retrieve the desired value.


It's not easy, it's not simple, but it's necessary.


Elaborate a bit more (a gigabyte more at least).


Regards!
 
Hi SirJB7

Thanks for the latest.

I think my main difficulty is that there are so many dates and prices for each room type that to create this on a spreadsheet (multiplied by numerous hotels) will have one huge data entry task, apart from being quite a large spreadsheet? If im hearing you right?

Im just trying to think how i input a rate for every date and every room type and every hotel? But if this is the way to go then i guess i must do that!

Am i thinking correctly here, sorry?

Cheers

WO
 
Hi, WO!

The main difficulty would only be worthy if you gain a major flexibility or an improved speed in the use of the data stored in that sheet/s. It's tedious, time consuming, but's the only way to automatize your whole quotation activities. I guess that's up to you to decide if you go on with the new model or you continue as today.

Consider that not only it's hard the initial data entry, but maintenance as well depending on how frequently your providers change prices.

You're thinking correctly, I believe.

Regards!
 
Thanks SirJB7, i think you have answered my query...trying to automate a proceedure which in reality is possibly just as quick to manually compile the data on front sheet!

Cheers

WO
 
Hi, WO!

Glad you help you. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top