Do a VLOOKUP between two dates, and then return a corresponding value?

Mr. Snrub

Board Regular
Joined
May 22, 2005
Messages
147
Office Version
  1. 365
Platform
  1. Windows
I am using Excel 2016. I have a table with three columns: "Start Date", "End Date", and "Season". The table looks as follows:
<p>
<table>
<tr>
<th>Start Date</th>
<th>End Date</th>
<th>Season</th>
</tr>
<tr>
<td>1/1/2018</td>
<td>3/19/2018</td>
<td>Winter</td>
</tr>
<tr>
<td>3/20/2018</td>
<td>6/20/2018</td>
<td>Spring</td>
</tr>
<tr>
<td>6/21/2018</td>
<td>9/21/2018</td>
<td>Summer</td>
</tr>
<tr>
<td>9/22/2018</td>
<td>12/21/2018</td>
<td>Autumn</td>
</tr>
<tr>
<td>12/22/2018</td>
<td>12/31/2018</td>
<td>Winter</td>
</tr>
</table>
</p>

So I want the user to enter in a date and then lookup the season (or if it's out of range, output N/A or "Invalid"):

<p>
<table>
<tr><td>7/18/2018</td></tr>
<tr><td>???</td></tr>
</table>
</p>

How do I do this?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Excel 2010
ABC
1Start DateEnd DateSeason
21/1/20183/19/2018Winter
33/20/20186/20/2018Spring
46/21/20189/21/2018Summer
59/22/201812/21/2018Autumn
612/22/201812/31/2018Winter
7
8
97/18/2018
10Summer
Sheet3
Cell Formulas
RangeFormula
A10{=INDEX($C$2:$C$6,MATCH(A9,IF($A$2:$A$6<=A9,IF($B$2:$B$6>=A9,$B$2:$B$6)),-1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
I figured it out by just using the regular LOOKUP function since the dates were sorted. By the way, how did you get that Excel spreadsheet in your post? That looks much better than my HTML table tags.
 
Last edited:
Upvote 0
https://onedrive.live.com/?id=8CFFDEC0CE27E813!189&cid=8CFFDEC0CE27E813

Download the first folder, extract, then go to file-options-add ins-manage add ins-browse-click on the file wherever you saved it-be sure it's checked in the add ins, and it should appear on the ribbon if you have Excel 2007 or later, otherwise in the tools menu.

To post on the board with it, highlight the range (not too large, there's like a 100 row limit and too many columns are hard to see), then go to add-ins - mrexcel htlml - generate html - go to MrExcel message page and control-V
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top