excelexcel-formulaarray-formulas

Excel array formula to return names of empty columns

I have the following table called cdc in a sheet called magic.

Excel table with four columns and 7 rows, two columns are empty

My input table can vary in size since it's the output of a Power Query, but I assume it won't extend more than A1:Z100 when in production.

In cell A1 of sheet result, I need an Excel array formula that returns only the names of empty columns in the input table. In this specific example, it should return {"C", "D"). The resulting array should return only column names, not 0s or anything else.

In sheet result, the output of the array formula should look like this:

array formula resulting range, with just the names of empty columns

It's a pretty straightforward problem, but quite tricky.

Thanks for your help.

Solution

  • Try the following formula-

    =TOCOL(FILTER(Table1[#Headers],BYCOL(Table1,LAMBDA(col,SUM(--(col<>""))))=0))

    enter image description here