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))

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.
best response confirmed by excel_learner (Contributor)


=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))

Brilliant Thank you Hans

@Hans Vogelaar 

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?


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.