Multiple (8+) IF Statements

AYelverton

New Member
Joined
Dec 16, 2004
Messages
3
I am trying to produce a formula that will enable me to determine what period a date falls into within defined 28 day boundries

In column A I have the start date of each period, in column B the end date for each period. In C the period number.

For example

01/05/04 28/05/04 1

In L I have a list of dates that will a random date

In M I wish it to return what period that date falls into (1-13)

Typically I would use a multiple IF statement but as this is not possible for over 8 functions I am struggling to identify what is required

Thanks
Anthony
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi Anthony, welcome to the board!


Not sure I follow how your data is laid out entirely. But have a look at the VLOOKUP and INDEX/MATCH functions. They may be of use to you. If you need more, try posting a representative sample of your data using the HTML Maker (link at the bottom of the page).
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Hi Anthony,

Someone asked me this question today - was it you?

Assuming your date periods don't overlap, i.e. each period starts the day after the previous one finishes and that they are in ascending order then in M1 use =VLOOKUP(L1,A1:C32,3,1) and fill down.

HTH
 

AYelverton

New Member
Joined
Dec 16, 2004
Messages
3
Please below... Its not the exact layout but you can get the jist..
The column with the dates listed would typically be more random.



<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2">
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>Start</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>End</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>Period</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>Date & Time</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>Period</nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>21/01/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>17/02/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>1</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>21/01/2004 12:21</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>?</nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>18/02/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>16/03/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>2</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>21/01/2004 12:55</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>17/03/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>13/04/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>3</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>22/01/2004 08:05</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>14/04/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>11/05/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>4</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>22/01/2004 09:01</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>12/05/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>08/06/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>5</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>22/01/2004 11:10</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>09/06/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>06/07/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>6</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>22/01/2004 13:26</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>07/07/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>03/08/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>7</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>23/01/2004 08:48</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>04/08/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>31/08/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>8</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>23/01/2004 10:02</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>01/09/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>28/09/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>9</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>23/01/2004 14:52</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>29/09/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>26/10/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>10</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>23/01/2004 15:47</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>27/10/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>23/11/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>11</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>24/01/2004 08:02</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>24/11/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>21/12/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>12</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>25/01/2004 09:46</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>22/12/2004</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>18/01/2005</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>13</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>25/01/2004 11:19</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>26/01/2004 10:30</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>26/01/2004 14:05</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>26/01/2004 14:27</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>27/01/2004 10:56</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>27/01/2004 12:59</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>28/01/2004 08:13</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>28/01/2004 08:18</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>28/01/2004 15:40</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>30/01/2004 11:53</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>30/01/2004 15:48</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>30/01/2004 16:36</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>02/02/2004 09:37</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>02/02/2004 10:27</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr>03/02/2004 08:40</nobr></font></td>
<td align="center" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
</table>
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

Hi Anthony,

All your example dates fall in Period 1.

Try this:
Book1
ABCDE
121/01/200417/02/2004121/01/200412:211
218/02/200416/03/2004221/01/200412:551
317/03/200413/04/2004322/01/200408:051
414/04/200411/05/2004422/01/200409:011
512/05/20048/06/2004522/01/200411:101
69/06/20046/07/2004622/01/200413:261
77/07/20043/08/2004723/01/200408:481
84/08/200431/08/2004823/01/200410:021
91/09/200428/09/2004923/01/200414:521
1029/09/200426/10/20041023/01/200415:471
1127/10/200423/11/20041124/01/200408:021
1224/11/200421/12/20041225/01/200409:461
1322/12/200418/01/20051325/01/200411:191
1426/01/200410:301
1527/01/200410:561
1627/01/200412:591
1728/01/200408:131
1828/01/200408:181
1928/01/200415:401
2030/01/200411:531
2130/01/200415:481
2230/01/200416:361
232/02/200409:371
242/02/200410:271
253/02/200408:401
2625/06/200412:236
2711/11/200411:1111
Sheet2


Formula in E1 is : =VLOOKUP(D1,$A$1:$C$32,3,1) copied down
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Hi again,

Formula in E1 should be =VLOOKUP(D1,$A$1:$C$13,3,1)

I added two more dates outside period 1 to see if it worked.
 

AYelverton

New Member
Joined
Dec 16, 2004
Messages
3
Thanks guys

The data supplied was only a sample and you are right to assume that it will be for other periods.

I think this was one of those cases when I was trying to look for a high powered function when the answer was under my nose the whole time!!

Thanks again for your help

Anthony
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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
Top