How to look up most recent field info while ignoring empty fields

[hannahpipp]

Our Airtable base collects information about our program. Two of the tables track people and the programs that people are registered for. For more unchanging information--like name, email, or date of birth--we have fields on the people table. However, for information that can change from year to year--like city, job title, or phone number-- we collect it on the program registration table. In the past, we set up our base so the most recent records for a given person in the program registration table would update lookup fields in the people table, so the people table would display the most recent city, job title, phone number, etc. 

This system has worked well for us, but we are running into an issue where if the most recent record in the program registration table contains blank fields that correspond with people table lookups, then those people table lookup fields will also be blank. We would like to figure out a solution where the most recent pieces of information are still being transferred to the people table, but if a given field is left blank, it does not override what is already present in the people table lookup. 

Is there a way to put conditionality on our most recent city formula field that appears on the program registration table, for example, so that it would go from this, IF({Most Recent?}, City), to having another condition excluding empty fields, and when that happens, it would look at the 2nd most recent record, 3rd most recent record, etc until it finds a field that is filled for that value to pull into the people table?

[Marcelo]

you can set up your look up field like this:

the names are in spanish, but you can set "only include...."

where "Column A" is not empty, and so on

[hannahpipp]

Hello,

Unfortunately, this solution will not work for us. The problem is there isn't just 1 field we'd need conditionality for and adding each field to this list will not allow for nuance when a record is created with an empty phone number, for example, but a new address. I don't want the entire record to be excluded but just the empty fields. In the below example from our people table, all of these conditions would need to be true for the record to sync with the people table, rather than it just pulling the filled fields from this list and leaving the blanks. 

I'm thinking our solution will need to be on the program registration table. Currently, we have field called most recent that puts a 1 for the most recent record for a given person and a 0 for older ones. In addition to this formula field, we have formula fields for each of the program registration fields that get displayed as lookups in the people table. 

I'd like each of these formula fields on the program reg table to have conditionality that ignores empty cells, and when the most recent record has an empty cell in the given field, it looks back at the previous records for that given person. 

I'm guessing we'll need to add another field that numbers a person's records, rather than just having the 0 and 1 binary. What is the best way to do this so we can allow the above formula to look at the 2nd most recent, 3rd most recent, etc for a given person?