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

hoops3335

New Member
Joined
Feb 3, 2016
Messages
11
Hi all,

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

7/1/20187/1/20197/1/20207/1/2021
RE Taxes6/30/20196/30/20206/30/20216/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
DateRE Taxes per SFTotal RE Taxes
ExpenseIncomeExpenseIncome
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

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
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)),"")




ABCDE
101/07/1801/07/1901/07/2001/07/21
230/06/1930/06/2030/06/2130/06/22
3Net$1.16/SF$1.19/SF$1.23/SF$1.27/SF
4Base Year$0.16/SF$0.19/SF$0.23/SF$0.27/SF
5
6
7
8
9
10
11
12
13DateRE Taxes per SF
14Expense
1501/04/18
1601/05/18
1701/06/18
1801/07/18$1.16/SF$0.16/SF
1801/08/18$1.16/SF$0.16/SF

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

hoops3335

New Member
Joined
Feb 3, 2016
Messages
11
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?




DateSq FtRE Taxes per SF Total RE Taxes
ExpenseIncomeIncomeExpenseIncomeIncome
4/1/201821,027 SF #VALUE!#VALUE!#VALUE!
5/1/201821,027 SF #VALUE!#VALUE!#VALUE!
6/1/201821,027 SF #VALUE!#VALUE!#VALUE!
7/1/201821,027 SF$5.10$5.10$107,238$107,238#VALUE!
8/1/201821,027 SF$5.10$5.10$107,238$107,238#VALUE!
9/1/201821,027 SF$5.10$5.10$107,238$107,238#VALUE!
10/1/201821,027 SF$5.10$5.10$107,238$107,238#VALUE!
11/1/201821,027 SF$5.10$5.10$107,238$107,238#VALUE!
12/1/201821,027 SF$5.10$5.10$107,238$107,238#VALUE!
1/1/201921,027 SF$5.10$5.10$107,238$107,238#VALUE!
2/1/201921,027 SF$5.10$5.10$107,238$107,238#VALUE!
3/1/201921,027 SF$5.10$5.10$107,238$107,238#VALUE!

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

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

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