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 is...

excel

HansVogelaar
Nov 06, 2021
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)

HansVogelaar

MVP

Nov 06, 2021

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)

SydneyCroud

Copper Contributor

Nov 06, 2021

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

Forum Discussion

IFS formula error