Using Numbers Lookup With Date Ranges

The Lookup function in Mac Numbers can be used to find a value based on another value. You can also use numerical and time ranges by simply stating the start of each range. In this example, we look at a rental rate sheet and calendar where the price for each date is populated from the the rates in another table.
Video Transcript: Here's an interesting numbers problem that I was asked about recently. Say that you have a rental property or perhaps manage a small hotel or something and you've got a spreadsheet that looks something like this. You've got rental rates and they change for different seasons. So, for instance, here at the beginning of the year $129 a night going up to $250 a night for the middle of the summer and you've got these different rate areas throughout the calendar year and the amounts.
Then you've got your calendar where you're going to fill in  with what the rate is each individual night and then you can add in who is reserved for that night. It is something nice here that you can print or look at or something like that. Now the great thing would be is to have these numbers here automatically populate the calendar below so that if you change the rate, say for early spring, you wouldn't have to change all the dates here in the calendar. It would just happen automatically. 
Well, you can do this with the Lookup function. The Lookup function allows you to lookup something in a table like this. So, for instance, you could lookup the word winter and get the value 129. Lookup early spring and get 152. Lookup also lets you do ranges but not like you think. Because here's a range and it would be great to be able to lookup February 28 and find that it fits in this range. But this isn't a range here. This is a piece of text. It doesn't even know that these are real dates. How do you do ranges with the Lookup function? Well, you can do them using just one date and that would be like the starting date for the range. 
So I've actually created a column here and I've hidden it so you can pretend that I've just created it now, Unhide All Columns. I've created this column here with a start date for each range. These are actually dates here. You can tell when I click here if I look at the bottom left it says this  text. If I click here it says this is an actual date and its got a full time there. So this date range starts December 31, 2016. This range starts March 18, 2017. What happens when you try to do a lookup on a date it says, say if you're doing February 28, it'll find that this is the one it matches and only if its March 18 and after will it match this one. So using the dates down here I can then lookup in this table and get the correct number. 
So the way I want to do that is to use two different functions. One is the date function and the other is the lookup function. So let's see how the date function works. Here on the left I have a month and that's an actual date there so when I have just January, I just type January, it automatically converted that to January 1, 2017. So it actually has a month, day, and year. This is February 1, 2017. So I have the month, day, and year but the day is actually going to be from here which is just a number. So I want to take the month and the year from here and the day from here. So the way I can do that is let's put the formula here. I'll put = and I'll say date and date takes the year first. So I'm going to get year from the year function, year of this date right there then comma, then I'm going to get the month from the same cell comma but I'm going to get the day from this cell there. So it should give me January 2017 on the 7th and sure enough that's what I get here. It gives me, you can see, 1/7/17 right there. So that's great!
Now if I wanted to be able to copy and paste that everywhere I need to do some work on that formula. So here instead of having this cell reference move I want to make sure it preserves the column so it always stays in column A. The same for this one, for the month. It has got to stay in column A. But this one I don't need to preserve anything. I want it to move the column so it's always directly above the actual cell. So if I copy and paste it here then I get January 8. If I copy and paste all across then I get the proper date there. So that's great, that's a great way to start. 
But I don't want just the date there. I want it to lookup the rate. So let's go back to just this one cell here. So I'm going to go and modify this formula. I'm going to feed this date into a Lookup and let's see what lookup looks like. If I search over here for formulas you can see Lookup will actually do three parameters. Search, for, Search where, and the Result Values. So search for is indeed the date. That's my first parameter. Comma. The second parameter is going to be this column here. Search for in the Start Date. The third parameter is going to be in the rate right there. That's the Result and put a parenthsis. Now I want to also make sure that I preserve for that and preserve the column for that because it's not going to change columns. It's not going to shift over one to the right if I copy and paste one cell to the right. I want it to stay, these two columns. 
Now when I hit return I get 129. Great. Let's copy that and paste it across January. You can see it's all 129 which makes sense. I'm going to paste that in February as well. So I'm moving down these number of cells so it should, instead of looking for January 3 it will look for February 3 for that cell. Paste it in here I get 129. Let me go and paste it across for those twenty eight days and you can see it's still 129 which is correct because this rate goes all the way to March 18. Now let's go to March here and paste in all thirty one days in March. Now I can see it's 129 up until the 17th and on the 18th it's 152 which is exactly what it should be. I can continue to paste it down here. Let's paste it all the way across for these dates for the entire calendar, etc. So you can continue down throughout the calendar.
Now let's say if I want to change the rate. Maybe 152 is wrong and I want to change it to 149. You can see all of those changed to 149. It didn't stick with the right format here so let's change that to currency and you can see it puts it on there. So all I had to do is actually change the number here and it automatically changed it on my calendar. As 2017 goes into 2018 I can continue to add more to this table here. So I can go from December 30 to, you know, March something of 2018. I can extend the calendar down here. I can maybe, perhaps, even at some point save this out as 2017's rental rates and then do 2018 rental rates and change all these dates to 2018 dates. Change these to 2018 if I want. But the bottom line is that now I have something with the rate can actually be reflected from this table to this table.
Using Numbers Lookup With Date Ranges

Comments: One Response to “Using Numbers Lookup With Date Ranges”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information
