Forum Discussion
IF statement Formula not working
I'm trying to get a value in my category column with based on criteria in columns Y, Z, AC and AD.
The value imp trying to in Category column is in Reference Table tab.
I have tried to make a IFS statement to bring back values based on each criteria, but it keeps coming back with something wrong with formula. I have attached the file any help on how this formula can work.
This is the formula I have used:
=IFS([@Tier2]='Reference Table'!$E$40,'Reference Table'!$G$40,AND([@Tier2]='Reference Table'!$G$38),[@Policy]='Reference Table'!$F$38),'Reference Table'!$G$38,AND([@Tier2]='Reference Table'!$E$39,[@Policy]='Reference Table'!$F$39),'Reference Table'!$G$39,[@Tier2]="Tier 1",VLOOKUP([@Material]&[@Banding]&[@Tier2],CHOOSE({1,2},'Reference Table'!$C$2:$C$40&'Reference Table'!$E$2:$E$40,'Reference Table'!G2:G40),2,FALSE))
=INDEX('Reference Table'!$G$2:$G$34,MATCH(1,IFS([@Tier2]="Tier 1",('Reference Table'!$A$2:$A$34=[@Material])*('Reference Table'!$B$2:$B$34=[@Banding])*('Reference Table'!$E$2:$E$34=[@Tier2]),[@Tier2]="Tier 3",--('Reference Table'!$E$2:$E$34=[@Tier2]),[@Tier2]="Tier 2",('Reference Table'!$E$2:$E$34=[@Tier2])*('Reference Table'!$F$2:$F$34=[@Policy])),0))
6 Replies
Why do only two of the rows of the reference table have a value in the Policy/Non Policy column?
- Its because the rest are not dictated by that criteria only rows 38 and 39 are, so I have left the rest of the rows blank in the policy column.
=INDEX('Reference Table'!$G$2:$G$34,MATCH(1,IFS([@Tier2]="Tier 1",('Reference Table'!$A$2:$A$34=[@Material])*('Reference Table'!$B$2:$B$34=[@Banding])*('Reference Table'!$E$2:$E$34=[@Tier2]),[@Tier2]="Tier 3",--('Reference Table'!$E$2:$E$34=[@Tier2]),[@Tier2]="Tier 2",('Reference Table'!$E$2:$E$34=[@Tier2])*('Reference Table'!$F$2:$F$34=[@Policy])),0))
