Struggling with Nested formula

[excel_learner]

Hi does anyone know how I would write this Formula:

in column AP I'm trying to get the relevant category for that cell from my "reference table" column G.

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.

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.

Thank you

[#2]

@excel_learner 

=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.

[#3]

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 ?

[#4 · Accepted solution]

@excel_learner 

=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. 

[#5]

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.

[#6]

@excel_learner 

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.