IFS Formula help

Contributor

Hi,

Im trying to write a IFS formula With the attached file.

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've attached the file I'm referring to, any guidance would be much appreciated.

 

Thank you

 

5 Replies

@excel_learner 

On Sheet1, all rows with Non-Policy have Tier1. But there is no row for Non-Policy and Tier1 on the Reference Table sheet. What gives?

@excel_learner 

=IFS([@Tier4]='Reference Table'!$E$44,'Reference Table'!$G$44,

AND([@Tier4]='Reference Table'!$E$42,[@Policy]='Reference Table'!$F$42),'Reference Table'!$G$42,

AND([@Tier4]='Reference Table'!$E$43,[@Policy]='Reference Table'!$F$43),'Reference Table'!$G$43,

[@Tier4]="Tier 1",

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 can work with IFS formula only in excel online but i know the syntax of this formula and i tried to adapt IF formula to IFS. I hope it works.

Hi
That is because Tier 1 and Tier 3 are not effected by Policy/Non Policy
meaning i dont want to show categories for Tier 1 thats dictated by Policy/non policy criteria

@excel_learner 

 

If you have the most recent version of Excel, you could start by sifting through column G of your Reference table tab to get only one instance of each value there. That can be done very easily with the new function UNIQUE, and to have it sorted, just add SORT, as in

=SORT(UNIQUE(G2:G44))

 

That shorter list could be used in your IFS function. I am going to defer now to Hans for the rest of your inquiry.

Thank You It worked !
What does the CHOOSE({1,2} actually do ? Its all new to me

=IFS([@Tier4]='Reference Table'!$E$44,'Reference Table'!$G$44,AND([@Tier4]='Reference Table'!$E$42,[@Policy]='Reference Table'!$F$42),'Reference Table'!$G$42,AND([@Tier4]='Reference Table'!$E$43,[@Policy]='Reference Table'!$F$43),'Reference Table'!$G$43,[@Tier4]="Tier 1",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)