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.
=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.
What is your thought process when tackling this task, it would help me massively to know the best ways to go about these nested formulas.
I start with a simple IF statement. Then I add one statement at a time as shown in the attached example file.
If you work with Office365 or 2021 you can use IFS instead of IF. The syntax of IFS is easier than the syntax of IF. However i do only nest formulas if it's necessary. Sometimes VLOOKUP (or XLOOKUP....) can be applied instead of a nested formula or it might be possible to use Power Query.