Nov 10 2021 08:30 AM
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
Nov 10 2021 08:47 AM
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?
Nov 10 2021 09:03 AM
=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.
Nov 10 2021 09:04 AM
Nov 10 2021 09:33 AM
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.
Nov 10 2021 09:38 AM