Forum Discussion
IFS Formula help
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
=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.
- 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)
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?
- 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 criteriaIf 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.
