Return a value based on multiple cells
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!
Best Answer

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

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:
 Actual, 2. Updated, 3. Current, 4. Original
=IF(ISBLANK(Actual),Updated,IF(ISBLANK(Updated),Current,IF(ISBLANK(Current),Original,IF(ISBLANK(Original),"NA",Actual))))

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.

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

Many thanks!

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!