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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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).
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,219,006
Messages
6,145,711
Members
450,635
Latest member
Rookie3510

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