Nested Ifs Based on the Calendar Year

[tycasey17]

I have been trying to have a nested IF statement based on the year. It seems like the year portion is causing the issue but I cannot seem how to fix this, I am trying to have multiple years on the same sheet but have a formula to pull up by the year I select.


SAMPLE FORMULA:
IF(A1="2019",
IF(VLOOKUP(A2,B2:D10,2,FALSE)="YES","YES",
IF(VLOOKUP(A2,B2:D10,2,FALSE)="NO","NO","")),
IF(A1="2020",
IF(VLOOKUP(A2,B2:D10,3,FALSE)="YES","YES",
IF(VLOOKUP(A2,B2:D10,3,FALSE)="NO","NO","")),""))

 

[Fluff]

Why not simply
IF(A1=2019,VLOOKUP(A2,B2:D10,2,FALSE),IF(A1=2020,VLOOKUP(A2,B2:D10,3,FALSE),""))

 

[tycasey17]

There will be more than just two arguments, it will end up being about five per year so I see what you mean.

 

[jtakw]

Hi,

If you have the Year as a Number (Not Text) in A1, have you removed the quote marks around "2019", and "2020" in your formula like Fluff show in Post # 2 ?
If you have, then it should work.

Also, if you use a "Helper Cell" to convert the Year in A1 (2019, 2020) to 2, 3 respectively, your formula can be cut in half (or more), if you add more years, (i.e. 2019, 2020, 2021, 2022 to 2, 3, 4, 5).

See below sample:

Book1
	A	B	C	D	E	F	G
1	2019	2				YES	YES
2	some	this	a	b
3		that	b	c		^Your formula	Shortened using B1
4		some	yes	no
5		other	c	d
6		yes	d	e
7		no	e	f
8		maybe	f	g
9		high	g	h
10		low	h	i
Sheet554

Cell Formulas
Range	Formula
B1	=IF(A1=2019,2,IF(A1=2020,3,""))
F1	=IF(A1=2019,IF(VLOOKUP(A2,B2:D10,2,FALSE)="YES","YES",IF(VLOOKUP(A2,B2:D10,2,FALSE)="NO","NO","")),IF(A1=2020,IF(VLOOKUP(A2,B2:D10,3,FALSE)="YES","YES",IF(VLOOKUP(A2,B2:D10,3,FALSE)="NO","NO","")),""))
G1	=IFERROR(IF(VLOOKUP(A2,B2:D10,B1,FALSE)="YES","YES",IF(VLOOKUP(A2,B2:D10,B1,FALSE)="NO","NO","")),"")

 

[Fluff]

Or without a helper cell
=IFERROR(IF(VLOOKUP(A2,B2:D10,A1-2017,0)="yes","YES",IF(VLOOKUP(A2,B2:D10,A1-2017,0)="no","NO","")),"")