Nov 06 2021 02:17 PM
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)
Nov 06 2021 02:35 PM
SolutionTry
=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)
Nov 06 2021 03:18 PM
@Hans VogelaarYou are a genius, Hans. That worked! I appreciate the help! Thnak you
Nov 06 2021 02:35 PM
SolutionTry
=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)