Excel Formula to compare two columns for values, if none populate with data from 3rd column

Use the IF and ISBLANK functions together to check if specific cells in two columns are empty. If both are blank, the formula returns a value from a third column; otherwise, it leaves the cell empty or displays a custom message. This approach allows you to automate data population based on conditional logic without manual entry.

Understanding the Logic

The core requirement is to evaluate two conditions simultaneously. You need to determine whether Cell A1 and Cell B1 are both empty. In Excel, an empty cell can be tested using the ISBLANK function. However, ISBLANK only returns TRUE for truly empty cells. If a cell contains a zero-length string (""), ISBLANK returns FALSE. Therefore, you must decide if your "empty" state includes formulas that return "". If you want to treat both truly empty cells and cells with empty strings as blank, use the double unary operator (--) or compare against "" directly. The most robust method for general users is checking if the cell equals an empty string. The logic flows like this: IF (Condition 1 AND Condition 2) THEN Result FROM Column C ELSE Empty.

Constructing the Formula

Assume your data structure is as follows: - Column A: First value set - Column B: Second value set - Column C: Source data to populate if A and B are empty - Column D: Where the result should appear For row 2, the formula in cell D2 would be:

=IF(AND(A2="", B2=""), C2, "")

This formula uses the AND function to combine two checks. The first check verifies if A2 is empty. The second check verifies if B2 is empty. Only when both are true does the IF statement proceed to return the value from C2. If either A2 or B2 contains data, the formula returns an empty string (""). If you prefer to handle cases where cells might contain spaces or invisible characters, you can use the TRIM function inside the check:

=IF(AND(TRIM(A2)="", TRIM(B2)=""), C2, "")

This ensures that cells with only whitespace are also treated as empty.

Applying the Formula to Your Dataset

Once you have entered the formula in the first row of your output column, you need to apply it to the rest of the dataset. Select the cell containing the formula (e.g., D2). Hover over the bottom-right corner of the cell until the cursor turns into a thin black cross, known as the fill handle. Click and drag the fill handle down to the last row of your data. Alternatively, you can copy the cell (Ctrl+C) and paste it (Ctrl+V) into the range below. Excel automatically adjusts the relative references. So, if you drag down to row 10, the formula in D10 will become =IF(AND(A10="", B10=""), C10, ""). This dynamic adjustment is crucial for maintaining accuracy across large datasets. If your source data in Column C is static or referenced from another sheet, ensure that those references remain consistent. If you are pulling data from a different worksheet, you may need to adjust the reference to include the sheet name, such as 'Sheet2'!C2.

Handling Errors and Edge Cases

Sometimes, cells may not appear empty but contain errors like #N/A or #VALUE!. The standard ISBLANK or equality check will fail in these scenarios. To create a more resilient formula, you can wrap the checks in an IFERROR function or use COUNTA. Using COUNTA counts the number of non-empty cells. If COUNTA(A2:B2) equals 0, then both cells are effectively empty. This method ignores text strings that look empty but aren't technically blank.

=IF(COUNTA(A2:B2)=0, C2, "")

This is often cleaner than nested AND statements. It reduces the chance of syntax errors and makes the formula easier to read. Choose the method that best fits how your data is entered. If users manually type nothing, A2="" works. If they use dropdowns that leave cells blank, COUNTA is safer.

Frequently Asked Questions

What if one column has data and the other doesn't?

The formula will return an empty string because the AND condition requires both cells to be empty. If you want to populate the cell if ONLY ONE is empty, change AND to OR in the formula.

How do I handle cells with spaces instead of being truly empty?

Use the TRIM function to remove leading and trailing spaces before comparing. For example, use TRIM(A2)="" to ensure cells with only spaces are treated as empty.

Can I use this formula with VLOOKUP?

Yes, you can nest the IF statement inside a VLOOKUP or use it as the lookup_value argument. However, simpler logic usually performs better in large sheets.

Why is my formula returning #NAME? error?

This error typically occurs if there is a typo in the function name, such as writing "ANd" instead of "AND". Ensure all function names are spelled correctly and enclosed in parentheses.

Is there a way to highlight cells that were populated by this rule?

You can use Conditional Formatting to highlight cells in the result column that match values from Column C. Go to Home > Conditional Formatting > New Rule and define the criteria accordingly.