SWITCH formula with Multiple Criteria

[alphabex18]

Hi all, 

I have the below reference table that I need to build a conditional formula. I want to use a SWITCH statement, because its easier, but i don't know how to add in an "AND" statement to it. 

Should be "IF (Segment = DTP01) AND (Criteria = "382") then result would be "Group First Enrollment Date". then the next would be IF DTP01 and 007 would be "Group Policy Effective Date". 

If this cant be done in a SWITCH formula, then i can do a multiple IF statement, it just allows for so much error. I also tried the REPLACE tool, but couldn't figure out how to use 2 conditions

Segment	Criteria	Column Header
DTP01	382	Group First Enrollment Date
DTP01	007	Group Policy Effective Date
DTP01	301	Member Qualifying Effective Date for COBRA
DTP01	303	RAF Effective Date
DTP01	340	COBRA Effective Date
DTP01	356	Member First Enrollment Date
DTP01	357	Member Termination Date
DTP01	303	Maintenance Effective Date
DTP01	348	Plan Effective Date
DTP01	349	Plan Termination Date
DTP01	300	Effective Date with Carrier - No Break in Coverage

[danrh · Accepted solution]

If you already have a reference table setup, I'd use a Text Input and a Join instead of a Switch statement --- this would be easier to read/manage.  But if your requirements for whatever reason require a switch, I'd suggest concatenating the two fields first and basing the results on that.  Something like:

Switch([Segment]+'|'+[Criteria],'',
	'DTP01|382','Group First Enrollment Date',
	'DTP01|007','Group Policy Effective Date')

Hope it helps!

[alphabex18]

Actually, id like to build this as a text and join, so i don't have to write so many if statements. I am working on that now