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.
=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)
This is the formula i entered in your workbook. I have to enter formula as arrayformula with ctrl+shift+enter as i don't work with Office365 or 2021.
However i can't include criteria of column AM (Policy / No Policy) in the criteria range as there is no correponding column in the Reference Table.
- Thanks for your help
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.
