IF + AND + VLOOKUP Formula

[KingtheJones]

Hello,

First time post, long time user here so please be kind if I don't follow correct protocol.

I am creating a forecast budget and need to amend to below formula to include a VLOOKUP parameter.

My current formula works to produce the $ value from A2 if the date in B2 falls between the date options in C2 and D2:
<d$1),$a2,0))
=(IF(AND($B3>=C$2,$B3< d$2),$a3,0))

I would like to amend the spreadsheet so that B2 now contains one of two text options (fortnightly or monthly). I have set up a new tab with those two headings and beneath the monthly a list of dates (A2 = 01/06/18, A3 = 01/07/18 and so on) and the fortnightly column starts at 2/05/18(B2), B3 = 30/05/18 etc.

I cannot for the life of me add a VLOOKUP parameter and have the formula work.
The first half of the below table shows how the first formula works and the second half (in red) is what I want it to look like/do. Using the VLOOKUP data in the second table at the end:

	A	B	C	D	E	F	G
1	Amount	Date OR	WEEK STARTING
2	$	Period	30/04/2018	7/05/2018	14/05/2018	21/05/2018	28/05/2018
3	1,000	1/05/2018	1,000	-	-	-	-
4	1,000	6/05/2018	-	1,000	-	-	-
5	1,000	21/05/2018	-	-	-	1,000	-
6	1,000	Monthly	1,000	-	-	-	1,000
7	1,000	Monthly	1,000	-	-	-	1,000
8	1,000	Fortnightly	1,000	-	1,000	-	1,000

<tbody>
</tbody>

VLOOKUP DATA:

Monthly	Fortnightly
1/06/2018	16/05/2018
1/07/2018	30/05/2018
1/08/2018	13/06/2018
1/09/2018	27/06/2018
1/10/2018	11/07/2018
1/11/2018	25/07/2018
1/12/2018	8/08/2018
1/01/2019	22/08/2018
1/02/2019	5/09/2018
1/03/2019	19/09/2018
1/04/2019	3/10/2018
1/05/2019	17/10/2018
1/06/2019	31/10/2018
1/07/2019	14/11/2018
1/08/2019	28/11/2018
1/09/2019	12/12/2018
1/10/2019	26/12/2018
1/11/2019	9/01/2019
1/12/2019	23/01/2019
1/01/2020	6/02/2019
1/02/2020	20/02/2019

<tbody>
</tbody>

Any help would be amazing; thank you!
ray:

Regards,
Loren </d$2),$a3,0))></d$1),$a2,0))
:wink:

 

[AlanY]

needs some clarification.

how's the 1,000 in C6 come from?

 

[KingtheJones]

Ah, I'm a donkey; second tab VLOOKUP data should have started back further:

VLOOKUP DATA:

Monthly	Fortnightly
1/04/2018	18/04/2018
1/05/2018	2/05/2018

<tbody>
</tbody>

1/06/2018	16/05/2018
1/07/2018	30/05/2018
1/08/2018	13/06/2018
1/09/2018	27/06/2018
1/10/2018	11/07/2018

<tbody>
</tbody>

But please note that I wrote the 1,000 in there --- the formula didn't work so the amount wasn't formulated (for lack of a better word).

 

[KingtheJones]

And 4D should have been in 4C -- fixed below:

	A	B	C	D	E	F	G
1	Amount	Date OR	WEEK STARTING
2	$	Period	30/04/2018	7/05/2018	14/05/2018	21/05/2018	28/05/2018
3	1,000	1/05/2018	1,000	-	-	-	-
4	1,000	6/05/2018	1,000	<--	-	-	-
5	1,000	21/05/2018	-	-	-	1,000	-
6	1,000	Monthly	1,000	-	-	-	1,000
7	1,000	Monthly	1,000	-	-	-	1,000
8	1,000	Fortnightly	1,000	-	1,000	-	1,000

<tbody>
</tbody>

VLOOKUP DATA:
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">VLOOKUP DATA:</body>