Find first non-blank cell with INDEX/MATCH


Hi guys!

I'm pulling 2 columns, each with multiples of the same value, and some rows have blanks in the cells until the reference sheet is updated with said information. My INDEX/MATCH works great, except there it pulls the blank values, and thereby reducing the accuracy of my sheet.

The formula is as follows; =IF(ISBLANK([Region Name]@row), "", INDEX({Office RP}, MATCH([Region Name]@row, {Office Region}, 0)))

{Office RP} is the column that contains the blank cells on the reference sheet.

A challenge if it does needs to be addressed; I cannot manipulate the reference sheet as that is updated nightly from a SQL connector.

I searched and found some INDEX/COLLECT formulas, and tried, then failed. I was getting #INCORRECT ARGUMENT SET at each iteration.

How can I get the formula to pull in the first non-blank cell value?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!