VLOOKUP formula with multiple IF statements

SaraWitch

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
378
Office Version
  1. 365
Platform
  1. 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).
MrExcel Queries.xlsm
ABCDEFGHIJK
108/01/2024Table
2Date PlacedDate EndedStart YearEnd YearStart April MondayEnd April MondayMonthsLast SundayFirst MondayFiscal Period
302/04/20232022/23=IF(A3="","",IF(B3="",VLOOKUP($A$1,FiscalPeriod,4,1)),IF(B3>0,VLOOKUP(B3,FiscalPeriod,4,1)))03/04/202303/04/202305/04/202104/04/202105/04/20212021/22
402/04/20232022/23Should be 2023/202403/04/202303/04/202301/05/202104/04/202105/04/20212021/22
502/04/202310/04/20242022/232024/2503/04/202303/04/202301/06/202104/04/202105/04/20212021/22
6    01/07/202104/04/202105/04/20212021/22
7    01/08/202104/04/202105/04/20212021/22
8    01/09/202104/04/202105/04/20212021/22
9    01/10/202104/04/202105/04/20212021/22
10    01/11/202104/04/202105/04/20212021/22
11    01/12/202104/04/202105/04/20212021/22
12    01/01/202204/04/202105/04/20212021/22
13    01/02/202204/04/202105/04/20212021/22
14    01/03/202204/04/202105/04/20212021/22
15    03/04/202204/04/202105/04/20212021/22
16    04/04/202203/04/202204/04/20222022/23
17    01/05/202203/04/202204/04/20222022/23
18    01/06/202203/04/202204/04/20222022/23
19    01/07/202203/04/202204/04/20222022/23
20    01/08/202203/04/202204/04/20222022/23
21    01/09/202203/04/202204/04/20222022/23
22    01/10/202203/04/202204/04/20222022/23
23    01/11/202203/04/202204/04/20222022/23
24    01/12/202203/04/202204/04/20222022/23
25    01/01/202303/04/202204/04/20222022/23
2601/02/202303/04/202204/04/20222022/23
2701/03/202303/04/202204/04/20222022/23
2802/04/202303/04/202204/04/20222022/23
2903/04/202302/04/202303/04/20232023/24
3001/05/202302/04/202303/04/20232023/24
3101/06/202302/04/202303/04/20232023/24
3201/07/202302/04/202303/04/20232023/24
3301/08/202302/04/202303/04/20232023/24
3401/09/202302/04/202303/04/20232023/24
3501/10/202302/04/202303/04/20232023/24
3601/11/202302/04/202303/04/20232023/24
3701/12/202302/04/202303/04/20232023/24
3801/01/202402/04/202303/04/20232023/24
3901/02/202402/04/202303/04/20232023/24
4001/03/202402/04/202303/04/20232023/24
4101/04/202431/03/202401/04/20242024/25
4201/05/202431/03/202401/04/20242024/25
4301/06/202431/03/202401/04/20242024/25
4401/07/202431/03/202401/04/20242024/25
4501/08/202431/03/202401/04/20242024/25
4601/09/202431/03/202401/04/20242024/25
4701/10/202431/03/202401/04/20242024/25
4801/11/202431/03/202401/04/20242024/25
4901/12/202431/03/202401/04/20242024/25
5001/01/202531/03/202401/04/20242024/25
5101/02/202531/03/202401/04/20242024/25
5201/03/202531/03/202401/04/20242024/25
5306/04/202531/03/202401/04/20242024/25
5407/04/202506/04/202507/04/20252025/26
5501/05/202506/04/202507/04/20252025/26
5601/06/202506/04/202507/04/20252025/26
5701/07/202506/04/202507/04/20252025/26
5801/08/202506/04/202507/04/20252025/26
5901/09/202506/04/202507/04/20252025/26
6001/10/202506/04/202507/04/20252025/26
6101/11/202506/04/202507/04/20252025/26
6201/12/202506/04/202507/04/20252025/26
6301/01/202606/04/202507/04/20252025/26
6401/02/202606/04/202507/04/20252025/26
6501/03/202606/04/202507/04/20252025/26
6605/04/202606/04/202507/04/20252025/26
6706/04/202605/04/202606/04/20262026/27
Sheet4
Cell Formulas
RangeFormula
A1A1=TODAY()
E3:E25E3=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:F25F3=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:I15I3=$H$3-DAY($H$3)+8-WEEKDAY($H$3-DAY($H$3))
J3:J15J3=$H$3-DAY($H$3)+8-WEEKDAY($H$3-DAY($H$3)+6)
K3:K67K3=IF(MONTH(J3)<=3,(YEAR(J3)-1)&"/"&RIGHT(YEAR(J3),2),YEAR(J3)&"/"&RIGHT((YEAR(J3)+1),2))
I16,I29,I54,I67I16=$H16-DAY($H16)+8-WEEKDAY($H16-DAY($H16))
J16,J29,J41,J54,J67J16=$H16-DAY($H16)+8-WEEKDAY($H16-DAY($H16)+6)
I17:I28I17=$H$16-DAY($H$16)+8-WEEKDAY($H$16-DAY($H$16))
J17:J28J17=$H$16-DAY($H$16)+8-WEEKDAY($H$16-DAY($H$16)+6)
I30:I40I30=$H$29-DAY($H$29)+8-WEEKDAY($H$29-DAY($H$29))
J30:J40J30=$H$29-DAY($H$29)+8-WEEKDAY($H$29-DAY($H$29)+6)
I41I41=EOMONTH($H40,0)-MOD(WEEKDAY(EOMONTH($H40,0),11),7)
I42:I53I42=EOMONTH($H$40,0)-MOD(WEEKDAY(EOMONTH($H$40,0),11),7)
J42:J53J42=$H$41-DAY($H$41)+8-WEEKDAY($H$41-DAY($H$41)+6)
I55:I66I55=$H$54-DAY($H$54)+8-WEEKDAY($H$54-DAY($H$54))
J55:J66J55=$H$54-DAY($H$54)+8-WEEKDAY($H$54-DAY($H$54)+6)
C3:C4,C5:D25C3=IFERROR(VLOOKUP(A3,FiscalPeriod,4,1),"")
Named Ranges
NameRefers ToCells
FiscalPeriod=Sheet4!$H$3:$K$67C3:C25, D5:D25, I3:J15

Any help would be appreciated :)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
I've just noticed I had an extra bracket in, which has resolved on my test sheet but not in my actual sheet which has formulas for start and end date - just going to replicate and post...
 
Upvote 0
Okay, ignore me completely!! 🤯

It's because my formula is B2 returns "0" not blank!
MrExcel Queries.xlsm
ABCDEFGHIJKL
108/01/2024
2Date PlacedDate EndedStart YearEnd YearStart April MondayEnd April MondayStart DateEnd DateStart DateEnd DateStart DateEnd Date
305/04/2021 2021/222023/2403/04/202303/04/202305/04/2021
Sheet4
Cell Formulas
RangeFormula
A1A1=TODAY()
A3A3=IF(G3="","",G3)
B3B3=IF(A3="",0,IF(K3>0,L3,IF(I3>0,J3,IF(G3>0,H3))))
C3C3=IFERROR(VLOOKUP(A3,FiscalPeriod,4,1),"")
D3D3=IF(A3="","",IF(B3=0,VLOOKUP($A$1,FiscalPeriod,4,1),IF(B3>0,VLOOKUP(B3,FiscalPeriod,4,1))))
E3E3=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"))))
F3F3=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"))))
Named Ranges
NameRefers ToCells
FiscalPeriod=Sheet4!$N$3:$Q$67C3:D3

Thank you anyway... :)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
434
dshtesy
D
SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
2K
Dave Patton
D
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
508
DanteAmor
DanteAmor
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
611
awoohaw
awoohaw
M
  • Solved
Sumifs Formula not working
2
Replies
15
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,992
Messages
6,182,225
Members
453,095
Latest member
SleepyAtlasHQ

Share this page

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
Back
Top