Jan 06 2022 01:48 PM
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))
Jan 06 2022 02:04 PM
Why do only two of the rows of the reference table have a value in the Policy/Non Policy column?
Jan 06 2022 02:08 PM
Jan 06 2022 02:21 PM
Solution=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))
Jan 07 2022 09:59 AM
What if I added a column for pressure in the Reference Table to have a criteria for pressure i have added rows for whichever row shows as "MP" in Pressure column.
How would I extend the formula to include this statement and have the new categories showing for MP?
Jan 07 2022 12:41 PM
You complicated it unnecessarily by changing the tiers to T1, T2 and T3 in the reference table but not on the master sheet.
And in the reference table, you have 4"-5" but on the master sheet it is 4-5", etc.
This is not workable. Please make the two sheets consistent, then post again.
Jan 06 2022 02:21 PM
Solution=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))