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:

[TABLE="class: grid, width: 533"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Amount [/TD]
[TD="align: center"]Date OR[/TD]
[TD="colspan: 5, align: center"]WEEK STARTING[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]$
[/TD]
[TD="align: center"]Period
[/TD]
[TD="align: center"]30/04/2018
[/TD]
[TD="align: center"]7/05/2018[/TD]
[TD="align: center"]14/05/2018[/TD]
[TD="align: center"]21/05/2018[/TD]
[TD="align: center"]28/05/2018[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]1/05/2018[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]6/05/2018[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]21/05/2018[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]-[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]Monthly
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]1,000
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]Monthly
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]1,000
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]Fortnightly
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]1,000[/TD]
[/TR]
</tbody>[/TABLE]

VLOOKUP DATA:

[TABLE="class: grid, width: 0"]
<tbody>[TR]
[TD="align: center"]Monthly
[/TD]
[TD="align: center"]Fortnightly [/TD]
[/TR]
[TR]
[TD="align: center"]1/06/2018[/TD]
[TD="align: center"]16/05/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/07/2018[/TD]
[TD="align: center"]30/05/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/08/2018[/TD]
[TD="align: center"]13/06/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/09/2018[/TD]
[TD="align: center"]27/06/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2018[/TD]
[TD="align: center"]11/07/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/11/2018[/TD]
[TD="align: center"]25/07/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/12/2018[/TD]
[TD="align: center"]8/08/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/01/2019[/TD]
[TD="align: center"]22/08/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/02/2019[/TD]
[TD="align: center"]5/09/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/03/2019[/TD]
[TD="align: center"]19/09/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/04/2019[/TD]
[TD="align: center"]3/10/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/05/2019[/TD]
[TD="align: center"]17/10/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/06/2019[/TD]
[TD="align: center"]31/10/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/07/2019[/TD]
[TD="align: center"]14/11/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/08/2019[/TD]
[TD="align: center"]28/11/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/09/2019[/TD]
[TD="align: center"]12/12/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2019[/TD]
[TD="align: center"]26/12/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/11/2019[/TD]
[TD="align: center"]9/01/2019[/TD]
[/TR]
[TR]
[TD="align: center"]1/12/2019[/TD]
[TD="align: center"]23/01/2019[/TD]
[/TR]
[TR]
[TD="align: center"]1/01/2020[/TD]
[TD="align: center"]6/02/2019[/TD]
[/TR]
[TR]
[TD="align: center"]1/02/2020[/TD]
[TD="align: center"]20/02/2019[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: cms_table_grid, width: 0"]
<tbody>[TR]
[TD="align: center"]Monthly[/TD]
[TD="align: center"]Fortnightly[/TD]
[/TR]
[TR]
[TD="align: center"]1/04/2018[/TD]
[TD="align: center"]18/04/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/05/2018[/TD]
[TD="align: center"]2/05/2018[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table_grid, width: 0"]
<tbody>[TR]
[TD="align: center"]1/06/2018[/TD]
[TD="align: center"]16/05/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/07/2018[/TD]
[TD="align: center"]30/05/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/08/2018[/TD]
[TD="align: center"]13/06/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/09/2018[/TD]
[TD="align: center"]27/06/2018[/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2018[/TD]
[TD="align: center"]11/07/2018[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Date OR[/TD]
[TD="colspan: 5, align: center"]WEEK STARTING[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]$
[/TD]
[TD="align: center"]Period
[/TD]
[TD="align: center"]30/04/2018
[/TD]
[TD="align: center"]7/05/2018[/TD]
[TD="align: center"]14/05/2018[/TD]
[TD="align: center"]21/05/2018[/TD]
[TD="align: center"]28/05/2018[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]1/05/2018[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]6/05/2018[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]<--[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]21/05/2018[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]-[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]Monthly
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]1,000
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]Monthly
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]1,000
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]Fortnightly
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]1,000
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]1,000[/TD]
[/TR]
</tbody>[/TABLE]


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