Forum Discussion
Struggling with Nested formula
=IF([@Tier4]='Reference Table'!$E$44,'Reference Table'!$G$44,IF(AND([@Tier4]='Reference Table'!$E$42,[@Policy]='Reference Table'!$F$42),'Reference Table'!$G$42,IF(AND([@Tier4]='Reference Table'!$E$43,[@Policy]='Reference Table'!$F$43),'Reference Table'!$G$43,VLOOKUP([@Material5]&[@Diameter2]&[@Tier4],CHOOSE({1,2},'Reference Table'!$C$2:$C$44&'Reference Table'!$E$2:$E$44,'Reference Table'!$G$2:$G$44),2,FALSE))))
I entered this formula and it seems to work now. Unfortunately i overlooked the entries for the 2 categories for Policy in the beginning. In the Reference Table i had to change " <=3" " to "<=3” " (delete space in the beginning and switch " to ”) to make the adjusted formula work for these entries as well.
I only have Policy criteria for 2 categories in the reference table. T2A and T2B in column G of the reference table. Is there a problem with that layout.
Also I was assuming there's shouldn't be any #N/A as all categories on reference table are covered for the criteria required. Do you know why there would be #N/A ?
=IF([@Tier4]='Reference Table'!$E$44,'Reference Table'!$G$44,IF(AND([@Tier4]='Reference Table'!$E$42,[@Policy]='Reference Table'!$F$42),'Reference Table'!$G$42,IF(AND([@Tier4]='Reference Table'!$E$43,[@Policy]='Reference Table'!$F$43),'Reference Table'!$G$43,VLOOKUP([@Material5]&[@Diameter2]&[@Tier4],CHOOSE({1,2},'Reference Table'!$C$2:$C$44&'Reference Table'!$E$2:$E$44,'Reference Table'!$G$2:$G$44),2,FALSE))))
I entered this formula and it seems to work now. Unfortunately i overlooked the entries for the 2 categories for Policy in the beginning. In the Reference Table i had to change " <=3" " to "<=3” " (delete space in the beginning and switch " to ”) to make the adjusted formula work for these entries as well.