Hi all I’ve already posted this message, so apologies for repeating myself, but I don’t think I explained myself clearly enough the first time! I run holiday apartments in France, and am trying to find a way to use a simple Excel doc to calculate availability. I would like to be able to use a doc as mocked up below to check availability. I have created an array, A8:D21 to show the dates, then a column for each apartment we manage, the Quays and the Annex. When I have a booking, I would put the name of the person booking in the corresponding row. Jeremy Jones is booked into the “Quays” flat from 2 January 2005 – 12 January 2005. When there are no bookings, the columns have a 0 in them. When someone new enquires, I want to be able to enter their arrival and departure dates (such as B2 and B3 below) and allow the programme to check whether there are any bookings for both the arrival and end dates, and ALL dates in between. I know I can do =VLOOKUP((B2),(A8:D21),3,TRUE) to have the programme produce the name of anyone already booked for that apartment on the arrival date, and similarly =VLOOKUP((B3),(A8:D21),3,TRUE) for someone already booked on the departure date. But how can I tell Excel to do the following: =VLOOKUP((“any date between B2 and B3”),(A8:D21),3,TRUE). This is driving me nuts – a free night or two in the Quays or the Annex for someone who can put me out of my misery!! Thanks so much in advance. Jeremy A B C D 1 2 Arrival date 10 January 2005 3 Departure date 14 January 2005 4 5 6 7 Quays Annex 8 Saturday 01 January 2005 0 0 9 Sunday 02 January 2005 Jeremy Jones 0 10 Monday 03 January 2005 Jeremy Jones 0 11 Tuesday 04 January 2005 Jeremy Jones 0 12 Wednesday 05 January 2005 Jeremy Jones 0 13 Thursday 06 January 2005 Jeremy Jones 0 14 Friday 07 January 2005 Jeremy Jones 0 15 Saturday 08 January 2005 Jeremy Jones 0 16 Sunday 09 January 2005 Jeremy Jones 0 17 Monday 10 January 2005 Jeremy Jones 0 18 Tuesday 11 January 2005 Jeremy Jones 0 19 Wednesday 12 January 2005 Jeremy Jones 0 20 Thursday 13 January 2005 0 0 21 Friday 14 January 2005 0 0

You need to use offset along with vlookup. Check back soon for a solution.

I think I have something that works. In the cell below the arr/dep dates, put in =value(days360(B2,B3)). This gives the # days of the stay. In a cell to the right of arrival date, enter the text Available? In the cell below that enter the following =IF(COUNTIF(OFFSET(F6:F27,0,0,B4,1),"NO")>0,"no","yes") In F6 enter the following =IF(VLOOKUP(B$1,A$8:B$373,2)<>0,"NO","YES") In F7 enter the following =IF(VLOOKUP(B$1+1,A$8:B$373,2)<>0,"NO","YES") notice the increment of the vlookup reference cell. In F8 and remaining, increment by 1. This array will be a 3 week window from the arrival date with the word YES if the day is available and NO if it is unavailable; and the cell with the countif formula will tell you if any of the dates you are interested in are taken. You may need to change some of the hard values in the offset function by 1 to get the correct range returned.

Well, a couple of thoughts. One, I think you should re-evaluate the structure of your table. Instead of looking in the "name" column to determine if a unit is available, I think you should create another column for availability. The availability column could have the formula "=IF(name='',1,0)" where name is the name cell for that row. If no one is booked for that room, leavet he name field blank rather than have a 0. The reason I would do this is that then when you want to check a range of cells for availability you could then do a simple SUM function. If the value of the sum is 0, then the room is available for the entire period, otherwise it is at least partially booked. Now, to the second part. Looking up the sum of a range of cells based upon date where the date is a row header. I am going to assume that you are using the entire sheet for a complete year period and that Jan 1 always falls in row 8. Well instead of doing a lookup based upon the text "value" of the date, you could instead find the range based upon the data since you know where Jan 1 is in the worksheet. You can first find the number of days needed by subtracting the arrival date from the departure data and adding 1. You can then find the cell to start from "relative" to jan 1 by subtracting Jan 1 from the arrival date. Then you can use those values in the OFFSET function within a SUM function and get back the number of days that are available. In you example you could do this: Change column D for the table to be "=IF(Bn<>"",1,0)" (where n is the same column number as the formula) Then create a cell for Days Needed: "=DepDate-AriDate+1" (where DepDate and AriDate are references to those two cells) Lastly, create a cell for the days available. This will tell you how many days of the planned stay are available (if less than the Days Needed then there is a conflict): "=SUM(OFFSET(D8,A2-A8,0,E2,1))" Where D8 is the "Avalable" cell for Jan 1 A2 is the arrival date A8 is the Jan 1 date E2 is the Days Needed cell from above Michael J

Grok Lobster and Michael J - Thanks so much for all your help so far - I really appreciate it. Will go off now and try the suggestions. Particularly like the idea of using 1's (and the sum function) to work out if there are any clashes of availability. Will report back. Jeremy

Thanks again to you both - I am now completely up and running. Used Grok's OFFSET idea, and Michael J's 1s and 0s for availability, and I'm now running very well. Exactly what I needed, thanks. Jeremy