Return a value based on multiple cells

MelissaSan

Hi,

I want to check a series of cells and return a value of one cell based upon the others not being blank.

If I have columns:

Original Current Updated Actual Most Accurate

I want to populate "Most Accurate" with the value in Actual.

If Actual is blank then I want to pull "Updated" cell value

If Updated is blank then pull value in CURRENT into "Most Accurate"

If Current is blank then pull value in ORIGINAL into "Most Accurate"

If Original is blank the return "NA"

The end goal copy the value that is in "Most Accurate" and link it to another sheet for a dashboard reporting.

Thanks!

Paul Newcome

Try this:

=IF(Actual@row <> "", Actual@row, IF(Current@row <> "", Current@row, IF(Original@row <> "", Original@row, "N/A")))

MichaelTCA

Hello @MelissaSan

This would be simple to use a nested IF statement.

The function depends on where the data is coming from; whether you can reference cells directly or use a lookup function to pull the values from another sheet.

Also, the return value will depend on the order in which we analyze each situation.

Following your post, I assume the order in which you want each situation analyzed would be:

1. Actual, 2. Updated, 3. Current, 4. Original

=IF(ISBLANK(Actual),Updated,IF(ISBLANK(Updated),Current,IF(ISBLANK(Current),Original,IF(ISBLANK(Original),"NA",Actual))))

MelissaSan

Thanks! I have a nested IF but I can't get it by the 2nd lookup:

=IF(ISBLANK(Actual@row), Current@row, IF(ISBLANK(Current@row), Original@row, IF(ISBLANK(Original@row), "NA", Actual@row)))

I should have shown my actual sheet and formula in my first post, sorry!

So in the last case I would want it to show "Melissa Test Value"

And if THAT is blank (so all Original, Current, Actual are blank), then I would show NA.

Paul Newcome

Try this:

=IF(Actual@row <> "", Actual@row, IF(Current@row <> "", Current@row, IF(Original@row <> "", Original@row, "N/A")))

MelissaSan

Many thanks!

Paul Newcome

Happy to help. 👍️