Return the most recent result that is not blank

Options

I have a collection of Smartsheets that I use to track lab testing for each lot# of product. I'm trying to use the following formula to pull the latest result from sheet "ResultImport".

=INDEX(COLLECT({ResultImport pH}, {ResultImport ProductLot}, ProductLot@row, {ResultImport CreatedDate}, MAX(COLLECT({ResultImport CreatedDate}, {ResultImport ProductLot}, ProductLot@row))), 1)

It works, but it returns a blank if two rows of results have the same day/time and Product-Lot, but the first one contains a blank (due to formatting / the way the results are delivered from the lab).

What I want is the latest result that is not blank. Any ideas? I thought about an IF(ISBLANK()) but I think it would need an increment, so I can't wrap my head around that. Maybe I'm making it too complicated.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!