VLOOKUP formula with multiple IF statements

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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

Forum statistics

Threads
1,224,567
Messages
6,179,562
Members
452,926
Latest member
rows and columns

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