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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!