# Return Value Based on Multiple if and table of dates & values

#### hoops3335

##### New Member
Hi all,

I am struggling to come with an equation for the following problem. Below is a table to illustrate.

 7/1/2018 7/1/2019 7/1/2020 7/1/2021 RE Taxes 6/30/2019 6/30/2020 6/30/2021 6/30/2022 \$1.16/SF \$1.19/SF \$1.23/SF \$1.27/SF \$0.16/SF \$0.19/SF \$0.23/SF \$0.27/SF Net/Base Year Date RE Taxes per SF Total RE Taxes Expense Income Expense Income 4/1/2018 #N/A -\$3,454.57 #N/A #REF! 5/1/2018 #N/A -\$3,454.57 #N/A #REF! 6/1/2018 #N/A -\$3,454.57 #N/A #REF! 7/1/2018 #N/A -\$3,454.57 #N/A #REF! 8/1/2018 #N/A -\$3,454.57 #N/A #REF!

<tbody>
</tbody>
Let's first look at the Bold Table.
I have a series of time periods with start and end dates. Below the Start and End dates are a set of \$ values.

Below the bold table is a set of dates. Now I want to be able to return a value that if the cell says "Net" and a Date falls within the period it will return the specified period value of the first row \$ amounts. If the Bold Says "Base Year" and a Date falls within the period it will return the specified period value of the second row \$ amounts.

Its seems what I am looking to do is have an If statement followed by an hlookup of a value inbetween the array of bolded start and end dates

Sounds confusing, but I hope that makes sense to somebody who gan help

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

##### Well-known Member
Hi ,

Try this , Control +Shift+Enter not just enter

B15=IFERROR(INDEX(\$B\$3:\$E\$3,MATCH(1,IF(A15>=\$B\$1:\$E\$1,IF(A15<=\$B\$2:\$E\$2,IF(\$A\$3="Net",1)),0),0)),"")
C15=IFERROR(INDEX(\$B\$4:\$E\$4,MATCH(1,IF(A15>=\$B\$1:\$E\$1,IF(A15<=\$B\$2:\$E\$2,IF(\$A\$4="Base Year",1)),0),0)),"")

 A B C D E 1 01/07/18 01/07/19 01/07/20 01/07/21 2 30/06/19 30/06/20 30/06/21 30/06/22 3 Net \$1.16/SF \$1.19/SF \$1.23/SF \$1.27/SF 4 Base Year \$0.16/SF \$0.19/SF \$0.23/SF \$0.27/SF 5 6 7 8 9 10 11 12 13 Date RE Taxes per SF 14 Expense 15 01/04/18 16 01/05/18 17 01/06/18 18 01/07/18 \$1.16/SF \$0.16/SF 18 01/08/18 \$1.16/SF \$0.16/SF

<colgroup><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>

#### hoops3335

##### New Member
Thank you!!! I got it to work in a round about way. However, now when I try to take the resulting # it gives me a #VALUE indication if there the cell is left blank.

The table below shows the SF being multiplied by the RE TAXES per SF and the subsequent value sign when there is no \$ amount

Any Suggested Resolution?

 Date Sq Ft RE Taxes per SF Total RE Taxes Expense Income Income Expense Income Income 4/1/2018 21,027 SF #VALUE! #VALUE! #VALUE! 5/1/2018 21,027 SF #VALUE! #VALUE! #VALUE! 6/1/2018 21,027 SF #VALUE! #VALUE! #VALUE! 7/1/2018 21,027 SF \$5.10 \$5.10 \$107,238 \$107,238 #VALUE! 8/1/2018 21,027 SF \$5.10 \$5.10 \$107,238 \$107,238 #VALUE! 9/1/2018 21,027 SF \$5.10 \$5.10 \$107,238 \$107,238 #VALUE! 10/1/2018 21,027 SF \$5.10 \$5.10 \$107,238 \$107,238 #VALUE! 11/1/2018 21,027 SF \$5.10 \$5.10 \$107,238 \$107,238 #VALUE! 12/1/2018 21,027 SF \$5.10 \$5.10 \$107,238 \$107,238 #VALUE! 1/1/2019 21,027 SF \$5.10 \$5.10 \$107,238 \$107,238 #VALUE! 2/1/2019 21,027 SF \$5.10 \$5.10 \$107,238 \$107,238 #VALUE! 3/1/2019 21,027 SF \$5.10 \$5.10 \$107,238 \$107,238 #VALUE!

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

##### Well-known Member
Can you wtire the formula that return #VALUE! ?
IFERROR or ISBLANK ...

Replies
3
Views
723
Replies
3
Views
303
Replies
20
Views
785
Replies
4
Views
414
Replies
2
Views
328

1,191,175
Messages
5,985,110
Members
439,940
Latest member

### 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.

### Which adblocker are you using?

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

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