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.
- Thank you so much.
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.