Help

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?

Topic Labels: Formulas
Solved
Jump to Solution
2394 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Brenda_Ginsberg
6 - Interface Innovator
6 - Interface Innovator

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?

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.

1 Solution

Accepted Solutions
Brenda_Ginsberg
6 - Interface Innovator
6 - Interface Innovator

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

6 Replies 6

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
6 - Interface Innovator
6 - Interface Innovator

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.

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.

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:

Capture_AirTable Nested IF Formula Issue to Solve_061520

Screenshot #2: Not accepting the 5 field formula:

Capture_AirTable Nested IF Formula Issue to Solve_Will Not Acceot Real Formula Need_061520

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):

Capture_AirTable Nested IF Formula Issue to Solve_Working Formula_061520

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.)

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?