VLOOKUP formula with multiple IF statements

SaraWitch

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
322
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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

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
841
Dave Patton
D
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
285
DanteAmor
DanteAmor
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
312
awoohaw
awoohaw
M
  • Solved
Sumifs Formula not working
2
Replies
15
Views
821
Peter_SSs
Peter_SSs
S
  • Question
Date column not working
Replies
4
Views
486
awoohaw
awoohaw

Forum statistics

Threads
1,215,071
Messages
6,122,964
Members
449,094
Latest member
Anshu121

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