How to Create Formula that Returns the Data in Field 1 (if data exists), or Field 2 (if no data in field 1 and has data in field 2), Same for Sequentially Fields 3-5?

Brenda_Ginsberg · ‎Jun 10, 2020

I see there are several posts on how to choose a “text” result by writing that text in the nested IF statement. However, I am looking to return the data in field 1 (check this field first and return the data in field 1 if data exists in field 1), and if no data exists in field 1 move on to check field 2 (check this field next and return the data in field 2 if data exists in field 2), and if no data exists in both fields 1 & 2 move on to check field 3 (check this field next and return the data in field 3 if data exists in field 3), and if no data exists in all three of fields 1 & 2 & 3 move on to check field 4 (check this field next and return the data in field 4 if data exists in field 4). There are 5 layers through field 5.

Thanks so much in advance for your insight and time! I appreciate it.

Brenda_Ginsberg · ‎Jun 11, 2020

Thanks so very much! When I enter the string for the first field, it works. When I add the 2nd (or onwards) it does not allow the formula to save. I have used the exact formula structure shared. Any solution? Much appreciation.

See Solution in Thread

ScottWorld · ‎Jun 10, 2020

IF({Field 1},{Field 1},
IF({Field 2},{Field 2},
IF({Field 3},{Field 3},
IF({Field 4},{Field 4},
IF({Field 5},{Field 5}
)))))

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

Brenda_Ginsberg · ‎Jun 11, 2020

Thanks so very much! When I enter the string for the first field, it works. When I add the 2nd (or onwards) it does not allow the formula to save. I have used the exact formula structure shared. Any solution? Much appreciation.

ScottWorld · ‎Jun 11, 2020

It’s probably something easy. Can you please copy & paste your formula here? And please send a screenshot of how your formula looks within the Airtable interface as well.

Brenda_Ginsberg · ‎Jun 15, 2020

Thanks so much @ScottWorld. Here is the screen shot of the formula. Fyi the field names are a little unusual because they have numbers in front that were from a multi-database field-mapping exercise that was used to migrate data to AirTable.

Screenshot #1: Formula Issue to Solve:

Screenshot #2: Not accepting the 5 field formula:

Screenshot #3: Works with checking 1 field (but need to get the goal above to work to check the 5 fields and return the master email address):

ScottWorld · ‎Jun 15, 2020

Here’s what I would do — rename all your fields to easy field names. For example, something super simple like: Email 1, Email 2, Email 3, Email 4, Email 5.

You’re adding way too much complexity by having all those extraneous symbols in your field names, which actually represent OTHER functions in Airtable.

And then, after you’ve done that, please paste your formula here as plain text, so I can copy & paste it into a text editor to see what’s wrong. It’s probably a missing parenthesis or missing comma or something easy like that.

(Alternatively, you can try just increasing your working formula from 1 field to 2 fields, and see if you can get it to work like that. If so, then slowly increase it next to 3 fields.)

Mariane_Marketi · ‎Sep 09, 2023

I keep getting this error message:

Sorry, there was a problem saving this field. Can’t save field because it causes a circular reference

Ps: My field 2 is a lookup field (don't know if this makes a difference)

Am I doing something wrong?