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

[hoops3335]

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

 

[admiral100]

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]

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>

 

[admiral100]

Can you wtire the formula that return #VALUE! ?
You can try to add IFERROR or ISBLANK ...