Multiple (8+) IF Statements

AYelverton · Jan 21, 2005

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

Zack Barresse · Jan 21, 2005

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 · Jan 21, 2005

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 · Jan 21, 2005

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">Start</td>
<td align="center" valign="bottom">End</td>
<td align="center" valign="bottom">Period</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">Date & Time</td>
<td align="center" valign="bottom">Period</td>
</tr>
<tr>
<td align="center" valign="bottom">21/01/2004</td>
<td align="center" valign="bottom">17/02/2004</td>
<td align="center" valign="bottom">1</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">21/01/2004 12:21</td>
<td align="center" valign="bottom">?</td>
</tr>
<tr>
<td align="center" valign="bottom">18/02/2004</td>
<td align="center" valign="bottom">16/03/2004</td>
<td align="center" valign="bottom">2</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">21/01/2004 12:55</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">17/03/2004</td>
<td align="center" valign="bottom">13/04/2004</td>
<td align="center" valign="bottom">3</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">22/01/2004 08:05</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">14/04/2004</td>
<td align="center" valign="bottom">11/05/2004</td>
<td align="center" valign="bottom">4</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">22/01/2004 09:01</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">12/05/2004</td>
<td align="center" valign="bottom">08/06/2004</td>
<td align="center" valign="bottom">5</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">22/01/2004 11:10</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">09/06/2004</td>
<td align="center" valign="bottom">06/07/2004</td>
<td align="center" valign="bottom">6</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">22/01/2004 13:26</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">07/07/2004</td>
<td align="center" valign="bottom">03/08/2004</td>
<td align="center" valign="bottom">7</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">23/01/2004 08:48</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">04/08/2004</td>
<td align="center" valign="bottom">31/08/2004</td>
<td align="center" valign="bottom">8</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">23/01/2004 10:02</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">01/09/2004</td>
<td align="center" valign="bottom">28/09/2004</td>
<td align="center" valign="bottom">9</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">23/01/2004 14:52</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">29/09/2004</td>
<td align="center" valign="bottom">26/10/2004</td>
<td align="center" valign="bottom">10</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">23/01/2004 15:47</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">27/10/2004</td>
<td align="center" valign="bottom">23/11/2004</td>
<td align="center" valign="bottom">11</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">24/01/2004 08:02</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">24/11/2004</td>
<td align="center" valign="bottom">21/12/2004</td>
<td align="center" valign="bottom">12</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">25/01/2004 09:46</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom">22/12/2004</td>
<td align="center" valign="bottom">18/01/2005</td>
<td align="center" valign="bottom">13</td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">25/01/2004 11:19</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">26/01/2004 10:30</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">26/01/2004 14:05</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">26/01/2004 14:27</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">27/01/2004 10:56</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">27/01/2004 12:59</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">28/01/2004 08:13</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">28/01/2004 08:18</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">28/01/2004 15:40</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">30/01/2004 11:53</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">30/01/2004 15:48</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">30/01/2004 16:36</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">02/02/2004 09:37</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">02/02/2004 10:27</td>
<td align="center" valign="bottom"></td>
</tr>
<tr>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom"></td>
<td align="center" valign="bottom">03/02/2004 08:40</td>
<td align="center" valign="bottom"></td>
</tr>
</table>

Fergus · Jan 21, 2005

Hi Anthony,

All your example dates fall in Period 1.

Try this:

Book1

A

B

C

D

E

1

21/01/2004

17/02/2004

1

21/01/200412:21

2

21/01/200412:55

3

22/01/200408:05

4

22/01/200409:01

5

22/01/200411:10

6

22/01/200413:26

7

23/01/200408:48

8

23/01/200410:02

9

23/01/200414:52

10

23/01/200415:47

11

24/01/200408:02

12

25/01/200409:46

13

25/01/200411:19

14

26/01/200410:30

1

15

27/01/200410:56

1

16

27/01/200412:59

1

17

28/01/200408:13

1

18

28/01/200408:18

1

19

28/01/200415:40

1

20

30/01/200411:53

1

21

30/01/200415:48

1

22

30/01/200416:36

1

23

2/02/200409:37

1

24

2/02/200410:27

1

25

3/02/200408:40

1

26

25/06/200412:23

6

27

11/11/200411:11

11

Sheet2

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

Fergus · Jan 21, 2005

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 · Jan 21, 2005

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

Multiple (8+) IF Statements

AYelverton

New Member

Excel Facts

Zack Barresse

MrExcel MVP

Fergus

Well-known Member

AYelverton

New Member

Fergus

Well-known Member

Fergus

Well-known Member

AYelverton

New Member

Similar threads

Forum statistics

Share this page

Multiple (8+) IF Statements

AYelverton

New Member

Excel Facts

Zack Barresse

MrExcel MVP

Fergus

Well-known Member

AYelverton

New Member

Fergus

Well-known Member

Fergus

Well-known Member

AYelverton

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock