Specified formula cannot be entered because it uses more levels of nesting than are allowed

[xlsFan]

I have an IF/THEN, VLOOKUP formula that is tied to a dropdown list of months, but I'm running into the issue of exceeding the max of 7 levels. I have seen a lot of posts on switching to CHOOSE, INDEX, LOOKUP, MATCH, but I can't translate these solutions for my issue. Cell A3 is the month dropdown list. This is my first time posting, so I hope I'm following all of the rules. Thank you in advance.

=IF($A$3="All",VLOOKUP($B8,'2015'!$B$1:$AG$900,16,FALSE),IF($A$3="Jan",VLOOKUP($B8,'2015'!$B$1:$AG$900,4,FALSE), IF($A$3="Feb",VLOOKUP($B8,'2015'!$B$1:$AG$900,5,FALSE), IF($A$3="Mar",VLOOKUP($B8,'2015'!$B$1:$AG$900,6,FALSE), IF($A$3="Apr",VLOOKUP($B8,'2015'!$B$1:$AG$900,7,FALSE), IF($A$3="May",VLOOKUP($B8,'2015'!$B$1:$AG$900,8,FALSE), IF($A$3="Jun",VLOOKUP($B8,'2015'!$B$1:$AG$900,9,FALSE), IF($A$3="Jul",VLOOKUP($B8,'2015'!$B$1:$AG$900,10,FALSE), IF($A$3="Aug",VLOOKUP($B8,'2015'!$B$1:$AG$900,11,FALSE), IF($A$3="Sep",VLOOKUP($B8,'2015'!$B$1:$AG$900,12,FALSE), IF($A$3="Oct",VLOOKUP($B8,'2015'!$B$1:$AG$900,13,FALSE), IF($A$3="Nov",VLOOKUP($B8,'2015'!$B$1:$AG$900,14,FALSE), IF($A$3="Dec",VLOOKUP($B8,'2015'!$B$1:$AG$900,15,FALSE)

 

[Joe4]

Welcome to the Board!

Just use month to calculate which field to return in the 3rd argument of VLOOKUP, and you can get it down to 2, i.e.

=IF($A$3="All",VLOOKUP($B8,'2015'!$B$1:$AG$900,16,FALSE),VLOOKUP($B8,'2015'!$B$1:$AG$900,[COLOR=#ff0000]MONTH(DATEVALUE($A$3 & "/1"))+3[/COLOR],FALSE))

 

[Eric W]

Welcome to the board!

Try this:

=VLOOKUP($B8,'2015'!$B$1:$AG$900,MATCH($A$3,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","All"},0)+3,FALSE)

You can even do something like:

=VLOOKUP($B8,'2015'!$B$1:$AG$900,IFERROR(MONTH($A$3&1)+3,16),FALSE)

which is even shorter, but possibly not as clear.

 

[xlsFan]

Welcome to the Board!

Just use month to calculate which field to return in the 3rd argument of VLOOKUP, and you can get it down to 2, i.e.

=IF($A$3="All",VLOOKUP($B8,'2015'!$B$1:$AG$900,16,FALSE),VLOOKUP($B8,'2015'!$B$1:$AG$900,[COLOR=#ff0000]MONTH(DATEVALUE($A$3 & "/1"))+3[/COLOR],FALSE))

Thanks Joe! This worked like a charm for tabs 2015-2017, but in my 2018 tab the format changes. The part of the formula where you just add 3 to the month no longer returns the correct values. The new column index numbers are like this: Jan = 3, Feb = 6, Mar = 9... etc. So, adding 2 to Jan = 3 (correct), but then adding 2 to Feb = 4 (incorrect). Is there a correct modification for this?

 

[Joe4]

column index numbers are like this: Jan = 3, Feb = 6, Mar = 9... etc. So, adding 2 to Jan = 3 (correct), but then adding 2 to Feb = 4 (incorrect). Is there a correct modification for this?

So they are jumping by 3 for each month?
Then simply change the +3 part in red in the formula to *3, i.e.

=IF($A$3="All",VLOOKUP($B8,'2015'!$B$1:$AG$900,16,FALSE),VLOOKUP($B8,'2015'!$B$1:$AG$900,[COLOR=#ff0000]MONTH(DATEVALUE($A$3 & "/1"))*3[/COLOR],FALSE))

 

[xlsFan]

Wow. Now I'm really embarrassed. Must be time to take a walk. Thank you!!

 

[Joe4]

You are welcome!