SOLVED

IFS formula error

New Contributor

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

@SydneyCroud 

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)

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