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


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this...

    =IF([Region Name]@row <> "", INDEX(COLLECT({Office RP}, {Office RP}, @cell <> "", {Office Region}, @cell = [Region Name]@row), 1))

  • Thank you, Paul!

    I'm still scratching my head why yours works, and my implementation didn't.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    There are a number of reasons why yours could have been failing. One that I personally do quite frequently is I forget the first range in the COLLECT function. Between using a lot of COUNTIFS and the fact that you have to put the same range in twice it tends to get lost in the shuffle.

    ..............INDEX(COLLECT({Office RP}, {Office RP}, @cell <> "", {Off..........................

    Another one that I do regularly is spend so much time and energy on the COLLECT that I forget to specify that we want row 1 in the INDEX function.

    ...........INDEX(COLLECT({Office RP}, {Office RP}, @cell <> "", {Office Region}, @cell = [Region Name]@row), 1))

  • Paul, your explanation was as helpful as your solution. Thank you for taking the time. Michael

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!