SaraWitch
Active Member
- Joined
- Sep 29, 2015
- Messages
- 378
- Office Version
- 365
- Platform
- Windows
Hello peeps,
I want to do a VLOOKUP with multiple IF statements, but all the formulas I've tried so far don't work. In A1 is today's date. In A3 is a start date, in B3 an end. I have a VLOOKUP from a table in C3 which returns the start date fiscal year but I want a formula in D3 that if A3 is blank, return nothing, if B3 has a date return the fiscal year for that date, and if B3 is blank return the fiscal year from today's date (fiscal year in this case is the first Monday of each April).
Any help would be appreciated
I want to do a VLOOKUP with multiple IF statements, but all the formulas I've tried so far don't work. In A1 is today's date. In A3 is a start date, in B3 an end. I have a VLOOKUP from a table in C3 which returns the start date fiscal year but I want a formula in D3 that if A3 is blank, return nothing, if B3 has a date return the fiscal year for that date, and if B3 is blank return the fiscal year from today's date (fiscal year in this case is the first Monday of each April).
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =TODAY() |
E3:E25 | E3 | =IF(A3="","",LET(yc,DATE(YEAR(TODAY()),4,0),yp,DATE(YEAR(TODAY())-1,4,0),IF(A3<=yc,WORKDAY.INTL(yp,1,"0111111"),WORKDAY.INTL(yc,1,"0111111")))) |
F3:F25 | F3 | =IF(A3="","",LET(yc,DATE(YEAR(TODAY()),4,0),yp,DATE(YEAR(TODAY())-1,4,0),IF(OR(B3>"",A3<=yc),WORKDAY.INTL(yp,1,"0111111"),WORKDAY.INTL(yc,1,"0111111")))) |
I3:I15 | I3 | =$H$3-DAY($H$3)+8-WEEKDAY($H$3-DAY($H$3)) |
J3:J15 | J3 | =$H$3-DAY($H$3)+8-WEEKDAY($H$3-DAY($H$3)+6) |
K3:K67 | K3 | =IF(MONTH(J3)<=3,(YEAR(J3)-1)&"/"&RIGHT(YEAR(J3),2),YEAR(J3)&"/"&RIGHT((YEAR(J3)+1),2)) |
I16,I29,I54,I67 | I16 | =$H16-DAY($H16)+8-WEEKDAY($H16-DAY($H16)) |
J16,J29,J41,J54,J67 | J16 | =$H16-DAY($H16)+8-WEEKDAY($H16-DAY($H16)+6) |
I17:I28 | I17 | =$H$16-DAY($H$16)+8-WEEKDAY($H$16-DAY($H$16)) |
J17:J28 | J17 | =$H$16-DAY($H$16)+8-WEEKDAY($H$16-DAY($H$16)+6) |
I30:I40 | I30 | =$H$29-DAY($H$29)+8-WEEKDAY($H$29-DAY($H$29)) |
J30:J40 | J30 | =$H$29-DAY($H$29)+8-WEEKDAY($H$29-DAY($H$29)+6) |
I41 | I41 | =EOMONTH($H40,0)-MOD(WEEKDAY(EOMONTH($H40,0),11),7) |
I42:I53 | I42 | =EOMONTH($H$40,0)-MOD(WEEKDAY(EOMONTH($H$40,0),11),7) |
J42:J53 | J42 | =$H$41-DAY($H$41)+8-WEEKDAY($H$41-DAY($H$41)+6) |
I55:I66 | I55 | =$H$54-DAY($H$54)+8-WEEKDAY($H$54-DAY($H$54)) |
J55:J66 | J55 | =$H$54-DAY($H$54)+8-WEEKDAY($H$54-DAY($H$54)+6) |
C3:C4,C5:D25 | C3 | =IFERROR(VLOOKUP(A3,FiscalPeriod,4,1),"") |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
FiscalPeriod | =Sheet4!$H$3:$K$67 | C3:C25, D5:D25, I3:J15 |
Any help would be appreciated