Find first non-blank cell with INDEX/MATCH

Faaez Kamaal

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?

Paul Newcome

Try this...

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

Paul Newcome

Try this...

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

Faaez Kamaal

Thank you, Paul!

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

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))

Michael Campbell

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