Help

Using IF to look up field value, and return another field value

Topic Labels: Formulas
2392 4
cancel
Showing results for 
Search instead for 
Did you mean: 
John_Anthony
4 - Data Explorer
4 - Data Explorer

Hello,

First post and couldn’t find the answer. I’m trying to create a nested IF.

Field A, single select as choice 1, 2, or 3.
Field B, is a Date
Field C, is a Date
Field D, is a Date

I’d like to the formula to check the value of Field A.
If it’s 1, return date from B,
If it’s 2, return date from C
If it’s 3, return date from D

Thanks!

4 Replies 4
AlliAlosa
10 - Mercury
10 - Mercury

You’ll want to use a formula like…

SWITCH({Field A}, “1”, {Field B}, “2”, {Field C}, “3”, {Field D})

You’ll also probably want to use DATETIME_FORMAT() to get your dates formatted the way you want. :slightly_smiling_face:

Thanks. I keep getting errors. Here’s the exact fields.

Field is called Renewal Date. Formula below
Based off the formula, I want it to populate the date from the correct field.
“Renewal Type” is a single select. Options are P&C, Benefits, LFS.

So if P&C is selected, use the date in the “P&C RD” field.

SWITCH({Renewal Type}, “P&C”, {P&C RD}, “Benefits”, {B RD}, “LFS”, {LFS RD})

What am I missing?

Thanks!!

Your formatting looks correct, so perhaps you are just running into an issue with smart quotes being used in your formula, instead of dumb quotes – this can happen if you copy/pasted a formula out of the forums that has smart quotes in it (like @AlliAlosa’s does).

To remedy that, try clearing your formula entirely, and typing it in by hand into the formula field editor.

It could be the quotes as @Jeremy_Oglesby mentioned, but I am all of a sudden (as in within the last hour or so) returning errors on ALL of my fields that incorporate the SWITCH() function… I just started a new thread on this and am about to contact support!