Search code examples
google-sheetsgoogle-sheets-formula

Get the row number and column name of blank cells using arrayformula

I have two column L,M from rows 4-9.

Now I am trying to check if cells under these columns are blank or not. And if the cell or cells are blank , the result cell should display blank cell's row number and column name.

For e.g. if cell at row 6 column L is blank, output should be "Row: 6, Column: L is blank".

So I have used the formula :

  =ARRAYFORMULA(IF(ISBLANK(L5:L8)=TRUE,CONCATENATE("ROW: ",ROW(L5:M8)," COL: ",SUBSTITUTE(ADDRESS(3,COLUMN(L5:M8),4),"3","")),""))

However, when I use this formula I get an output like:

enter image description here

With the use of the above formula it is showing all the result in one cell.

The correct output should be - Row: 5, Col : L Row 5, Col : M

How do I update the formula so it gives the desired result?

Solution

  • You can use the ADDRESS function. The following formula returns the address of the blank cells in the range L5:M8.

    =ARRAYFORMULA(TEXTJOIN(", ",1,IF(L5:M8="",ADDRESS(ROW(L5:M8),COLUMN(L5:M8),4),)))

    Another solution using REDUCE:

    =REDUCE(,L5:M8,LAMBDA(a,c,TEXTJOIN(", ",1,a,IF(c="",ADDRESS(ROW(c),COLUMN(c),4),))))