SOLVED

New Contributor

# IFS formula error

I am using Excel 2019. I have a data value I want to evaluate, and depending upon the date, I am using a VLookup to do a calculation based on a percentage. I am getting an error stating my formula isn't correct, but I don't see an error. Any help would be appreciated.

IFS(J13<=DATE(2020,12,31),(M13*VLOOKUP(\$I13,'2.SalesTeam'!\$A\$2:\$D\$51,3,FALSE)),(J13>DATE(2020,12,31),J13<DATE(2021,4,1)),(M13*VLOOKUP(\$I13,'2.SalesTeam'!\$A\$2:\$D\$51,4,FALSE)),(J13=>DATE(2021,04,01),J13<DATE(2021,10,01)),(M13*VLOOKUP(\$I13,'2.SalesTeam'!\$A\$2:\$D\$51,5,FALSE)),(J13>DATE(2021,10,01)),(M13*VLOOKUP(\$I13,'2.SalesTeam'!\$A\$2:\$D\$51,6,FALSE)),TRUE,0)

2 Replies
best response confirmed by SydneyCroud (New Contributor)
Solution

# Re: IFS formula error

Try

=IFS(J13<=DATE(2020,12,31),M13*VLOOKUP(\$I13,'2.SalesTeam'!\$A\$2:\$F\$51,3,FALSE),J13<DATE(2021,4,1),M13*VLOOKUP(\$I13,'2.SalesTeam'!\$A\$2:\$F\$51,4,FALSE),J13<DATE(2021,10,1),M13*VLOOKUP(\$I13,'2.SalesTeam'!\$A\$2:\$F\$51,5,FALSE),TRUE,M13*VLOOKUP(\$I13,'2.SalesTeam'!\$A\$2:\$F\$51,6,FALSE))

or shorter

=M13*VLOOKUP(\$I13,'2.SalesTeam'!\$A\$2:\$F\$51,IFS(J13<=DATE(2020,12,31),3,J13<DATE(2021,4,1),4,J13<DATE(2021,10,1),5,TRUE,6),FALSE)

# Re: IFS formula error

@Hans VogelaarYou are a genius, Hans. That worked! I appreciate the help! Thnak you