Nested IF Statement to determine where in a table of date ranges a specific falls.

[Didaskale]

I have an array of Date Ranges (Beginning and End Dates in two columns); I need to determine in which "week" a specified resides, and display the Amount (which is held in a third column.

I have this formula to do so:

=IF(AND(B11 <G36 >F36),K36,0)

It works fine for one row in the array of dates, however when I replace the "else" argument with an exact copy of the formula (with the exception of "cell references" being changed I get an #NAME result.

=IF(AND(B11 <G36 >F36),K36,=IF(AND(B11 <G36 >F36),K36,0))

Any suggestions would be great.

 

[DanteAmor]

The formula does not look good

=IF(AND(B11 F36), K36.0)
B11 = F36
or
B11 <F36
or
B11 <> ""
and F36 <> ""?

You could explain with words what you need.

 

[Didaskale]

The formula does not look good

=IF(AND(B11 F36), K36.0)
B11 = F36
or
B11 <f36
or
B11 <> ""
and F36 <> ""?

You could explain with words what you need.

</f36

 

[Didaskale]

I do not understand your response.
Let me go into more detail on my need.

I have a date in B11.
In F9:F36 I have a series of Week Beginning Dates.
In G9:G36 I have a series of Week Ending Dates.
In K9:K36 I have a series of numbers.

I need to determine in which week B11 falls, and display the number in the corresponding row of Colum K.

Your help would be appreciated, my forte is Access and SQL, not Excel.

Thank you.​

 

[DanteAmor]

Sorry, something is happening on the forum page does not appear the symbols "equal".
Try the following:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "><b>Sheet</b></table><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:38.02px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:101.7px;" /><col style="width:82.69px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Beginning Dates</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Ending Dates</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Number</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">04-mar</td><td style="text-align:right; ">08-mar</td><td style="text-align:right; ">22</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">11-mar</td><td style="text-align:right; ">15-mar</td><td style="text-align:right; ">27</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="text-align:right; ">12-mar</td><td style="text-align:right; ">27</td><td > </td><td > </td><td style="text-align:right; ">18-mar</td><td style="text-align:right; ">22-mar</td><td style="text-align:right; ">32</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">25-mar</td><td style="text-align:right; ">29-mar</td><td style="text-align:right; ">37</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">01-abr</td><td style="text-align:right; ">05-abr</td><td style="text-align:right; ">42</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C11</td><td >=VLOOKUP(B11,F9:H13,3,1)</td></tr></table></td></tr></table> <br /><br />

 

[Didaskale]

Sorry, something is happening on the forum page does not appear the symbols "equal".
Try the following:

Sheet

	A	B	C	D	E	F	G	H

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:38.02px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:18.06px;"><col style="width:18.06px;"><col style="width:101.7px;"><col style="width:82.69px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Beginning Dates[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Ending Dates[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Number[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]04-mar[/TD]
[TD="align: right"]08-mar[/TD]
[TD="align: right"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]11-mar[/TD]
[TD="align: right"]15-mar[/TD]
[TD="align: right"]27[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]12-mar[/TD]
[TD="align: right"]27[/TD]

[TD="align: right"]18-mar[/TD]
[TD="align: right"]22-mar[/TD]
[TD="align: right"]32[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]25-mar[/TD]
[TD="align: right"]29-mar[/TD]
[TD="align: right"]37[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="align: right"]01-abr[/TD]
[TD="align: right"]05-abr[/TD]
[TD="align: right"]42[/TD]

</tbody>

Cell Formula C11 =VLOOKUP(B11,F9:H13,3,1) <tbody> </tbody>

<tbody>
</tbody>

Awesome, Thank you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.