Filling in columns in Excel. If x Is empty, fill with Y, if Y is empty fill with Z

Question

I am hoping you can help me with a more advanced formula for Excel.

I am looking to accomplish the below:

If Column T (New Account Owner) is Error(#NA) Fill Billing Country(D) with Shipping Country (I) and Fill Billing state with Shipping State (G).

If Column S (Use Shipping) is Error Fill Billing Country(D) with Apollo Country (M) and Filling Billing state with Apollo State (N).

But, it is important to not overwrite the current Billing Country if no Error in Column T.

enter image description here

Answer 1

From your post, it isn't clear what the order of priority is. Like if both New Account Owner and Use Shipping have an error, is 'Billing country' filled with 'Shipping country' or 'Apollo country'?

If you wanted to do this with just Excel, you would need to put the formula into the C and D columns and fill in the Billing Country and State manually, unless that data is retrieved from somewhere else, in which case you can just reference that.

These formulas only check New Account Owner for the #N/A error, no other errors.

Billing Country

=IF(ISNA(T2),IF(ISERROR(S2),M2,I2),"Country")

Billing State

=IF(ISNA(T2),IF(ISERROR(S2),N2,G2),"State")

• ISNA checks to see if that reference has the #N/A error
• ISERROR checks for any error in the reference value

Breakdown:
IF New Account Owner has #N/A error
AND Use Shipping has an error
THEN Billing Country/State = Apollo Country/State
ELSE THEN Billing Country/State = Shipping Country/State
ELSE Billing Country = "Country"

It really depends on how the data is being generated to if this is practical or not.