Discussion Re: Struggling with Nested formula in Excel
https://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947446#M121712
<P><LI-USER uid="1163132"></LI-USER> </P><P>=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))))</P><P> </P><P>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. </P>Wed, 10 Nov 2021 12:23:35 GMTQuadruple_Pawn2021-11-10T12:23:35ZStruggling with Nested formula
https://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947211#M121701
<P>Hi does anyone know how I would write this Formula:</P><P>in column AP I'm trying to get the relevant category for that cell from my "reference table" column G.</P><P>I want to use the criteria for columns AJ, AM, AN, AO then use these values against my reference table to see which category it falls into. I want to be able to use all the categories in column G from the reference table.</P><P>I know its something like a nested formula i would use but really struggling with how to structure this formula. I've attached the file I'm referring to, any guidance would be much appreciated.</P><P> </P><P>Thank you</P><P> </P>Wed, 10 Nov 2021 10:51:27 GMThttps://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947211#M121701excel_learner2021-11-10T10:51:27ZRe: Struggling with Nested formula
https://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947257#M121702
<P><LI-USER uid="1163132"></LI-USER> </P><P>=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)</P><P> </P><P>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.</P><P> </P><P>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.</P>Wed, 10 Nov 2021 11:16:28 GMThttps://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947257#M121702Quadruple_Pawn2021-11-10T11:16:28ZRe: Struggling with Nested formula
https://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947282#M121704
Thanks for your help<BR />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.<BR />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 ?<BR />Wed, 10 Nov 2021 11:28:37 GMThttps://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947282#M121704excel_learner2021-11-10T11:28:37ZRe: Struggling with Nested formula
https://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947446#M121712
<P><LI-USER uid="1163132"></LI-USER> </P><P>=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))))</P><P> </P><P>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. </P>Wed, 10 Nov 2021 12:23:35 GMThttps://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947446#M121712Quadruple_Pawn2021-11-10T12:23:35ZRe: Struggling with Nested formula
https://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947693#M121721
Thank you so much.<BR />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.<BR />Wed, 10 Nov 2021 13:35:05 GMThttps://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2947693#M121721excel_learner2021-11-10T13:35:05ZRe: Struggling with Nested formula
https://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2948053#M121731
<P><LI-USER uid="1163132"></LI-USER> </P><P>I start with a simple IF statement. Then I add one statement at a time as shown in the attached example file.</P><P> </P><P>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.</P>Wed, 10 Nov 2021 15:00:32 GMThttps://techcommunity.microsoft.com/t5/excel/struggling-with-nested-formula/m-p/2948053#M121731Quadruple_Pawn2021-11-10T15:00:32Z